How to identify the ID of row which has been modified?

[User 4700810]

Hi all, I have table Demo:

CREATE TABLE Demo
(
ID int PRIMARY KEY,
Name char(50),
BeModified int DEFAULT(0)
)

Whenever [Name] is updated, [BeModified] will be set to 1. I think I should create an TRIGGER AFTER UPDATE

CREATE TRIGGER UpdateRow

ON Demo

AFTER UPDATE

UPDATE Demo

Set BeModified = 1 WHERE ID = "???"

My problem is I do not know how to identify the ID of row which has been modified. Have you got any suggestion for me? Thank you so much!

[dan sh]

I don't think you really need a trigger here. You must be updating the name through a stored procedure/SQL query. Just make the query update the bool column as well.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

[Mycroft Holmes]

As d@nish recommended. There is also a keyword in the trigger that indicates the current record, not sure what it is, I NEVER use triggers, they are evil. As another idea I use a datetime field instead of a boolean, it tells you so much more than a simple yes/no.

Never underestimate the power of human stupidity RAH

[Member 4501940]

I concur. This is NOT the place for a trigger. I only use triggers to store 1)disposable, 2)derived and 3)complex data used for reporting or searching. Must meet all 3 criteria. But to answer the question-

UPDATE Demo
Set BeModified = 1 WHERE ID IN (SELECT ID FROM inserted)

OR

UPDATE Demo
Set BeModified = 1
FROM Demo
INNER JOIN inserted
ON Demo.ID = inserted.ID

This is assuming sql server. The "inserted" table is a virtual table which contains all of the fields and values from the insert, update or delete that fired the trigger. You might think that only a record at a time is updated but in fact since it is possible to update multiple records at the same time, this may NOT be what you want. But thats what you get with triggers. If you ever have to update all of the records in that table, BeModified will be set to 1 for all. Be warned.