Automatically Tracking Last Modification Date/time in Table Column
Lately I found a very simple and yet consistent way to track the last update made to a Sql Server database table. You can easily store the modification date and time through a table trigger that can set a specific column of the updated row(s) with the GETDATE() function.
In order to do that, create a new trigger for your table and use the Inserted virtual table to obtain the ID of the entry, in order to update it. We’ll use here a LastModified column, which will store a datetime:
CREATE TRIGGER last_modification_timestamp
AFTER INSERT, UPDATE
DECLARE @colID INT
SELECT @colID = (SELECT ID FROM Inserted)
UPDATE target_table SET LastModified = GETDATE() WHERE ID = @colID
So, we’re basically retrieving the column ID (primary key) from the affected row and using it to update the LastModified column with the current date and time.
This way, you can enforce this policy and guarantee that you’ll always get the correct date/time in that column, since the trigger will be automatically executed just after each INSERT and DELETE.
Hope this helps!