What's the easiest way to add automatic version information to MSSQL tables?

Question

Hi there,

The information should be 2 date time columns(inserted/updated) with ms precision and should be automatically updated by the server whenever someone inserts, or updates a row.

Answer 1

Add columns to your table

ALTER TABLE yourTable ADD
    Inserted datetime NULL,
    Updated datetime NULL
GO

Create an Update and Insert Trigger to update the columns

CREATE TRIGGER yourTableInsertTrigger 
   ON  yourTable
   AFTER INSERT
AS 
BEGIN
    Update yourTable Set Inserted = getdate()
    from Inserted 
    Where yourTable.Key = Inserted.Key
END
GO


CREATE TRIGGER yourTableUpdateTrigger 
   ON  yourTable
   AFTER UPDATE AS 
BEGIN
    Update yourTable Set Updated = getdate()
    from Updated
    Where yourTable.Key = Updated.Key
END
GO

Now if you want to be really clean, you'd make sure that these two columns couldn't be changed/updated by using views instead of direct table access for your other access to the data. Also if your primary keys aren't consistent, and you have many tables I'd suggest you use CodeGeneration to create the sql. MyGeneration would do nicely.

Answer 2

I think the inserted trigger is obsolete. Just add getdate() to the inserted column as a default value instead of null.