Click here to Skip to main content
15,887,464 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hey guys!
lets say i have 2 tables:
MainDoc        Cards
---------      ------------
ID (PK)         ID (PK) 
CustomerCode    CardName  
CustomerNumber  CardStatus
******          *****
----------     -----------


Now, i need to create a trigger (right now i dont mind if it will be in both of the tables or just in one, although i prefer to be just in one) that run depend on values from both MainDoc and Cards. for example, i need the trigger will fire only when CardStatus in 'Cards' changes to "Activated" and the CustomerNumber in 'MainDoc' = 5001.
to do that, i think i need to get access to both inserted and deleted tables of both tables. i'm kind of new to triggers. maybe i need to write LEFT JOIN in the trigger? is it even possible? here is an example of my trigger:

SQL
CREATE TRIGGER [dbo].[TMainDocStatus]
ON [dbo].[MainDoc]
 AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @val1Old nvarchar(4000), @val1New nvarchar(4000)
DECLARE @val2Old nvarchar(4000), @val2New nvarchar(4000)

SELECT @val1Old = CustomerNumber from deleted
SELECT @val1New = CustomerNumber from inserted
SELECT @val2Old = ?
SELECT @val2New = ?

declare @cmd varchar(8000)
set @cmd = 'C:\1\SmsWorker.exe '

IF @val1New = 5001 AND @val2New = 'Activated'
EXEC master ..xp_cmdshell @command = @cmd
END


As u can see, the trigger holds 2 variables for each table which holds the inserted and the deleted value. for start, i dont know how to access the inserted and deleted tables of a tables that not register in the trigger, second, how can i make the trigger to follow changes made in more then one table?
Thanks a head,
oron.
Posted
Comments
Mathi Mani 28-Jul-15 13:27pm    
Yes, you can write left join in trigger. AFAIK, it is not possible to create a trigger bound to more than one table. For your case, you should have some kind of relationship between the two tables and you can used that to get the data from the other table.
oronsultan 28-Jul-15 18:09pm    
ok, first thanks for your reply mathi. about your last comment, i dont use Relationships between tables. but, i do have a primary key in each table which is bond to the pk in others. i need to get the data from the otther table during the runtime of the trigger. u said i can write left join in trigger. can you please attach a code example on how am i suppouse to do that?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900