Trigger updating multiple records

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 2000 Forums

SQL Server Development (2000)

Trigger updating multiple records

Author

Topic

SQLError
Yak Posting Veteran

63 Posts

Posted - 2005-03-06 : 14:46:27

Hi, when a record in my table is updated, I have the following trigger:
---

UPDATE Corrections SET LastModified = GetDate()
WHERE RecordID = RecordID

Select RecordID from deleted

----

What this does though, is update all the columns in the table
because of the "RecordID = RecorID" statement.

I tried changing

Select RecordID as R from Deleted

but this did not work.

Can someone help me out.

thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-06 : 14:56:22

You have two unconnected statements here. One doing an update (of all rows) then one returning a resultset.

UPDATE Corrections SET LastModified = GetDate()
WHERE RecordID in (Select RecordID from deleted)

or better

UPDATE Corrections
SET LastModified = GetDate()
from Corrections c
join deleted d
on d.RecordID = c.RecordID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SQLError
Yak Posting Veteran

63 Posts

Posted - 2005-03-06 : 14:58:57

Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources