Sql Server (Entity Framework): created_at , updated_at Columns

Question

I think I should mention I'm trying to get Entity Framework\SQL server to do something that I was used to coming from Rails. I really just want to know the 'best' way to have automatic created_at & updated_at column values for records that I insert/update in the database.

Right now I've hooked into the ObjectContext.SavingChanges event and things are working well. After I wrote and tested my code however I realize that there may be a better or faster way to do this.

Answer 1

One method is to use insert/update triggers to set the created_at and updated_at columns to the current time.

The Insert trigger would look something like this:

CREATE TRIGGER [dbo].[inserted_myTable] ON [dbo].[myTable] AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    UPDATE [dbo].[myTable] Set AdmitDateTime = GetDate() where pkid in (SELECT pkid FROM Inserted)
END

The Update trigger would look something like this:

CREATE TRIGGER [dbo].[updated_myTable] ON  [dbo].[myTable] AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    UPDATE [dbo].[myTable] Set AdmitDateTime = GetDate() where pkid in (SELECT pkid FROM Inserted)
END

One advantage of the trigger approach is that the time/date will always be in the same time zone. Another advantage is that if someone modifies the database record outside of your application, the fields are still updated.