How to track when a row was created/updated automatically in SQL Server?

Question

Is there a way to automatically tell SQL Server that all tables should have a timestamp for insert and update?

That's probably a lot of repetitive work to do this?

Or is there another/more efficient way to do this with the transaction log?

Answer 1

You can have a RowCreated DATETIME DEFAULT (GETDATE()) to handle the date when the row was initially created automatically - but there's nothing short of writing a trigger to do the same for a RowModified DATETIME column.

For each table, you'd have to write an AFTER UPDATE trigger to handle the RowModified column something like this:

CREATE TRIGGER dbo.trg_YourTableUpdated
ON dbo.YourTable AFTER UPDATE
AS BEGIN
    UPDATE dbo.YourTable
    SET RowModified = GETDATE()
    FROM INSERTED i
    WHERE dbo.YourTable.ID = i.ID
END