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:
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.