How to add a “last updated” column in a SQL Server 2008 R2 table

sql-server-2008-r2triggers

I have a table in my SQL Server 2008 R2 database, and would like to add a column called LastUpdated, that will automatically be changed every time the row is updated. That way, I can see when each individual row was last updated.

It seems that SQL Server 2008 R2 doesn't have a data type to handle this like earlier versions did, so I'm not sure of the best way to do it. I wondered about using a trigger, but what would happen when the trigger updated the row? Will that fire the trigger again, etc?

Best Answer

To know which row was last updated, you need to create a new column of type DATETIME/DATETIME2 and update it with a trigger. There is no data type that automatically updates itself with date/time information every time the row is updated.

To avoid recursion you can use the UPDATE() clause inside the trigger, e.g.

ALTER TRIGGER dbo.SetLastUpdatedBusiness 
ON dbo.Businesses 
AFTER UPDATE -- not insert!
AS
BEGIN
    IF NOT UPDATE(LastUpdated)
    BEGIN
        UPDATE t
            SET t.LastUpdated = CURRENT_TIMESTAMP -- not dbo.LastUpdated!
            FROM dbo.Businesses AS t -- not b!
            INNER JOIN inserted AS i 
            ON t.ID = i.ID;
    END
END
GO

Related Solutions

Sql-server – Insert Update trigger how to determine if insert or update

Triggers have special INSERTED and DELETED tables to track "before" and "after" data. So you can use something like IF EXISTS (SELECT * FROM DELETED) to detect an update. You only have rows in DELETED on update, but there are always rows in INSERTED.

Look for "inserted" in CREATE TRIGGER.

Edit, 23 Nov 2011

After comment, this answer is only for INSERTED and UPDATED triggers.
Obviously, DELETE triggers can not have "always rows in INSERTED" as I said above

Sql – OptimisticConcurrencyException — SQL 2008 R2 Instead of Insert Trigger with Entity Framework

As you suspected, the problem is that any insertions into a table with an Identity column are immediately followed by a select of the scope_identity() to populate the associated value in the Entity Framework. The instead of trigger causes this second step to be missed, which leads to the 0 rows inserted error.

I found an answer in this StackOverflow thread that suggested adding the following line at the end of your trigger (in the case where the item is not matched and the Insert is performed).

select [Id] from [dbo].[TableXXX] where @@ROWCOUNT > 0 and [Id] = scope_identity()

I tested this with Entity Framework 4.1, and it solved the problem for me. I have copied my entire trigger creation here for completeness. With this trigger defenition I was able to add rows to the table by adding Address entities to the context and saving them using context.SaveChanges().

ALTER TRIGGER [dbo].[CalcGeoLoc]
   ON  [dbo].[Address]
   INSTEAD OF INSERT
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT OFF;

-- Insert statements for trigger here
INSERT INTO Address (Street, Street2, City, StateProvince, PostalCode, Latitude, Longitude, GeoLoc, Name)
SELECT Street, Street2, City, StateProvince, PostalCode, Latitude, Longitude, geography::Point(Latitude, Longitude, 4326), Name 
FROM Inserted;

select AddressId from [dbo].Address where @@ROWCOUNT > 0 and AddressId = scope_identity();
END

Best Answer

Related Solutions

Sql-server – Insert Update trigger how to determine if insert or update

Sql – OptimisticConcurrencyException — SQL 2008 R2 Instead of Insert Trigger with Entity Framework

Related Topic