Trigger and update to a row in SQL Server after it's been updated

Question

Is this the best way to keep a simple track of changes to a database row:

ALTER TRIGGER [dbo].[trg_121s] 
ON  [dbo].[121s]
  AFTER UPDATE
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here

update dbo.[121s]
set modified=getdate()
where id in 
(select distinct ID from Inserted)

END

So any update to a row in [121s] will result in the modified column being updated.

It works, but I'm not sure if it's the best way to achieve this.

I'm a litle confused over this line:

(select distinct ID from Inserted)

...and how it knows it's getting the correct row ID.

Thanks for any confirmation/clarification,

Mark

Answer 1

inserted is a pseudo-table and it definitely contains all the right rows that were affected by the UPDATE statement (and I assume DISTINCT isn't necessary, if ID a primary key - though it's hard to tell what the table is with a name like 121s). Whether all of them actually had changed values is another thing you may consider validating before applying the modified date/time. Barring that, I would probably do it this way:

ALTER TRIGGER [dbo].[trg_121s] 
ON [dbo].[121s]
AFTER UPDATE
AS 
BEGIN
  SET NOCOUNT ON;

  UPDATE t SET modified = CURRENT_TIMESTAMP
   FROM dbo.[121s] AS t
   WHERE EXISTS (SELECT 1 FROM inserted WHERE ID = t.ID);
   -- WHERE EXISTS is same as INNER JOIN inserted AS i ON t.ID = i.ID;
END
GO

If you want to have a 100% foolproof guarantee that they're all updated with the same timestamp (though I don't know if I've ever seen multiple values in this use case):

ALTER TRIGGER [dbo].[trg_121s] 
ON [dbo].[121s]
AFTER UPDATE
AS 
BEGIN
  SET NOCOUNT ON;

  DECLARE @ts DATETIME;
  SET @ts = CURRENT_TIMESTAMP;

  UPDATE t SET modified = @ts
   FROM dbo.[121s] AS t
  INNER JOIN inserted AS i 
  ON t.ID = i.ID;
END
GO

And if you want to ensure that the update only occurs if, say, the column foo changed value, you could say:

  UPDATE t SET modified = @ts
   FROM dbo.[121s] AS t
   INNER JOIN inserted AS i
   ON t.ID = i.ID
   AND t.foo <> i.foo;

That's the general pattern, but it becomes more complex if foo is nullable, since SQL Server won't be able to match on rows where one side has a value and the other doesn't (or both don't). In that case you would do this:

   AND 
   (
     t.foo <> i.foo
     OR (t.foo IS NULL AND i.foo IS NOT NULL)
     OR (t.foo IS NOT NULL AND i.foo IS NULL)
   );

Some people will say "I can just use COALESCE or ISNULL against some magic value" like this:

WHERE COALESCE(t.foo, 'magic') <> COALESCE(i.foo, 'magic')

...and I will warn you against this, because you'll constantly be searching for some magic value that can't exist in the data.

Answer 2

Inserted is a table that contains the rows affected by the operation which fired the trigger(insert/update). So your trigger here is correct. If the Id is primary key, then you do not need the distinct(select id from Inserted is enough). If Id is not primary key, then your trigger is false, as you might end up updating more then you should.