7

I have a table with DateCreated and DateUpdated columns and using Entity Framework to insert/update values to the database.

I need the DateCreated column to get the SQL Server's GetDate() value on insertion only.

DateUpdated column value should always get updated with current GetDate() value on insertion and update both.

For the DateCreated column, I've set StoreGeneratedPattern="Computed" and on SQL Server table I've set default value of the column to be GetDate(), which works nicely as expected.

For the DateUpdated column I could not find a way to get the GetDate() value automatically set each time an entry is updated. This value get's set only when an entry is inserted.

Could someone shed some light on this.

Eranga Dissanayaka
  • 1,930
  • 3
  • 24
  • 27
  • Have you tried `StoreGeneratedPattern="Identity"`? – João Angelo Dec 09 '11 at 15:47
  • 1
    why doesn't getdate as the db default for datecreated work? it shouldn't overwrite it if you don't update it... – Daniel Moses Dec 09 '11 at 15:53
  • I agree with DMoses - the default value is only populated on row creation. – Mark Stafford - MSFT Dec 09 '11 at 16:43
  • @DMosses, Mark Stafford, Yes both of you are correct. the db default value gets set only when an entry is inserted but not updates. Actually what I wanted was a solution for the 'DateUpdated' field not for the 'DateCreated' field. It's completely my bad. I've re worded the question now. – Eranga Dissanayaka Dec 10 '11 at 00:02

1 Answers1

7

If you want DateUpdated to be set by the database, you can use a trigger to set the value to getdate(). I believe EF will also get the value set by the trigger if you set StoreGeneratedPattern="Computed" for DateUpdated.

For reference, your trigger would look something like this (you'll have to update per your table's PK):

create trigger MyTable_UpdatedTrigger
on MyTable
for update
as
begin
    update t set DateUpdated = getdate()
    from MyTable t
        join inserted i on t.Id = i.Id
end
Jeff Ogata
  • 56,645
  • 19
  • 114
  • 127