I once more need help with a trigger.
this time, I have a trigger that fires on update/insert.
in case of an update, the trigger is supposed to check if the value of a certain column has changed. If so, it has to update a column in another table. both tables share the same key, the key consists of two colums.
My first try was
UPDATE tDocuLog SET UpdateTimestamp = getdate()
WHERE (id1, id2) IN (SELECT id1, id2 FROM inserted i INNER JOIN deleted d WHERE NOT(i.StateID = d.StateID))
I got a syntax error "near ','". Googling a bit I stumbled upon some articles/messages saying that (id1,id2) does not work for sqlserver. They suggested a join (but the examples in these articles dealt with insert statements so there were no examples how to use them with update statements).
Now I tried this
UPDATE tDocuLog INNER JOIN inserted ins ON (tDocuLog.id1= ins.id1AND tDocuLog.id2 = ins.id2) INNER JOIN deleted del ON (ins.id1 = del.id1 AND ins.id2 = del.id2)
SET UpdateTimestamp = getdate()
WHERE NOT(ins.StateID = del.StateID)
but here I also get a syntax error "near the keyword INNER"
Can anybody tell me what I'm doing wrong and how to do it right?
Thank you in advance
sth_Weird
EDIT: maybe I should have posted this in another subforum (as well as my last trigger question), I just noticed the .NET part of this subforum's name...sorry, I just read the stored proc etc. keywords in the description and thought it would match here (since I did not find the trigger keyword anywhere in the other descriptions)...feel free to move this thread to the right subforum
Help with UPDATE...WHERE..IN (c1,c2) like trigger
Posted - 2009-07-13 : 10:35:43
