Help with UPDATE...WHERE..IN (c1,c2) like trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Help with UPDATE...WHERE..IN (c1,c2) like trigger

Author

Topic

sth_Weird
Starting Member

38 Posts

Posted - 2009-07-13 : 10:35:43

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-13 : 14:10:19

[code]UPDATE t
SET t.UpdateTimestamp = getdate()
FROM tDocuLog t
INNER JOIN inserted ins ON (t.id1= ins.id1 AND t.id2 = ins.id2)
INNER JOIN deleted del ON (ins.id1 = del.id1 AND ins.id2 = del.id2)
WHERE (ins.StateID <> del.StateID)
[/code]

sth_Weird
Starting Member

38 Posts

Posted - 2009-07-14 : 02:47:22

Thank you, now it works.
I'm still confused with the sql syntax used in triggers (I keep thinking in "normal" query language, where, in this case, the from-part is not necessary).
sth_Weird

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-14 : 03:41:22

quote:
Originally posted by sth_Weird

Thank you, now it works.
I'm still confused with the sql syntax used in triggers (I keep thinking in "normal" query language, where, in this case, the from-part is not necessary).
sth_Weird

This type of sysntax is sopported only in SQL Server and MS Access

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources