17
votes

I have a table with 3 fields [ID, Name, LastUpdated].
LastUpdated has a default value of "GetDate() so it automatically fills itself when a new record is added.

When I instead run an UPDATE on TABLE, I would like to have this field reset itself to the current GetDate().

CREATE TRIGGER dbo.Table1_Updated
   ON  dbo.Table1
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.Table1 SET LastUpdated = GETDATE()
END
GO

But because I don't have a WHERE Clause, ALL records get updated.

QUESTION:
Where would I get the value of the ID of the updated record on a UPDATE Trigger?

Would the fact that I'm updating a field of the table inside the Trigger, re-call a new Trigger event (and so on) ?

3

3 Answers

28
votes

From 'INSERTED', table INSERTED is common to both the INSERT, UPDATE trigger.

CREATE TRIGGER dbo.Table1_Updated
ON dbo.Table1
FOR INSERT, UPDATE /* Fire this trigger when a row is INSERTed or UPDATEd */
AS BEGIN
  UPDATE dbo.Table1 SET dbo.Table1.LastUpdated = GETDATE()
  FROM INSERTED
  WHERE inserted.id=Table1.id
END
1
votes
Update table1
set LastUpdated = getdate()
from inserted i, table1 a
where i.pk1 = a.pk1
0
votes
CREATE TRIGGER dbo.refreshModifyDate 
      ON  tStoreCategoriesImages
      FOR INSERT, UPDATE
AS 
  BEGIN
    SET NOCOUNT ON;
        update t set t.ModifyDate = getdate() from tStoreCategoriesImages t 
        inner join inserted i on i.ID = t.ID
  END
GO