Identity of inserted/updated row in trigger

Question

I have the following trigger but need ti find the identity of the row so I don't update all records in the table. How can I get the identity of the affected row?

BEGIN
  UPDATE tb_Division SET  LastModified = GetDate() WHERE "id of inserted/updated row"
END

Answer 1

Have you looked at the id of the inserted logical table? You have to be careful when using triggers, as a trigger may be operating on more than one row:

UPDATE tb_Division AS td
   SET LastModified = GetDate() 
FROM INSERTED AS i
WHERE td.id = = i.id

See here for more details, and from MSDN:

DML triggers use the deleted and inserted logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is tried. The deleted and inserted tables hold the old values or new values of the rows that may be changed by the user action. For example, to retrieve all values in the deleted table, use:

Answer 2

Mind you - a trigger can deal with a ton of rows at once - you'll have to take that into account!

You need to join your table to be updated with the Inserted pseudo-column on that ID field:

UPDATE dbo.tb_Division 
SET LastModified = GetDate() 
FROM Inserted i
WHERE tb_Division.Id = i.Id

or something like that.

Answer 3

Since a trigger in MS SQL Server does not distinguish between single-record and multi-record operations, you should JOIN the table with the INSERTED pseudo table or use a subselect:

UPDATE tb_Division
SET LastModified = GETDATE()
WHERE id IN (SELECT id FROM INSERTED)

id being the primary key column of your table.