Skip to content
Advertisement

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

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

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:

…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:

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):

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

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:

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

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

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement