How to track when a row was created/updated automatically in SQL Server?
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?
Have a look at AutoAudit
This will add columns to a table and create DML triggers to manage those columns as well as maintain the audit trail table
I've found it to be very useful
It's certainly possible to use DDL triggers to attempt to enforce table design issues. It's also possible to automate checking the metadata for design smells
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