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?

Carl

Answers


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

Need Your Help

Flash real-time information connection over wifi (lan?)

flash actionscript-3 communication wifi real-time

I would like to create a flash-based app for a tablet that sends messages to a 'receiver' app on the same wifi network. So I can have a bunch of tablets with app A that send info to app B, all on the

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.