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
Advertisement
Answer
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.