locked
Automatically set record last updated in SQL Server table via EF rather than DB trigger RRS feed

  • Question

  • I have about 50 tables where I want to just add a column called LastUpdated.  Is there a good way to automatically do the same thing as this trigger on a column called LastUpdated using EF?  Do I need to create the trigger on each of the tables or is there a good way to update the column each time a record is updated using functionality already in the EF?

    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
    Thanks!


    • Edited by Matt S- Wednesday, October 29, 2014 2:57 PM clarification
    Wednesday, October 29, 2014 2:56 PM

Answers

  • Your best option is to use the DB Trigger, becuase otherwise, you are going to have to do a Now() in VB or C# code and populate the Entity's property before doing a SaveChanges().
    • Proposed as answer by Fred Bao Thursday, October 30, 2014 9:08 AM
    • Marked as answer by Matt S- Friday, October 31, 2014 6:34 PM
    Wednesday, October 29, 2014 3:46 PM

All replies

  • Your best option is to use the DB Trigger, becuase otherwise, you are going to have to do a Now() in VB or C# code and populate the Entity's property before doing a SaveChanges().
    • Proposed as answer by Fred Bao Thursday, October 30, 2014 9:08 AM
    • Marked as answer by Matt S- Friday, October 31, 2014 6:34 PM
    Wednesday, October 29, 2014 3:46 PM
  • For whatever it's worth I put this together.  I have a simple identity field as the PK for each of my tables that always has a name like [tablename]ID so this works for me.

    -- add trigger to all tables with a LastUpdated column to automatically update the 
    BEGIN
        DECLARE @SQL NVARCHAR(MAX)
    		,
    		  @Schema VARCHAR(100)
    		,
    		  @Table VARCHAR(100)
    
        DECLARE c_LastUpdatedTables CURSOR FAST_FORWARD
    	   FOR
    		  SELECT TABLE_SCHEMA,
    			    TABLE_NAME
    		    FROM INFORMATION_SCHEMA.COLUMNS
    			    WHERE COLUMN_NAME = 'LastUpdated'
    				 AND DATA_TYPE = 'datetime'
    
        OPEN c_LastUpdatedTables
        FETCH next FROM c_LastUpdatedTables INTO @Schema, @Table
    
        WHILE @@FETCH_STATUS = 0
    	   BEGIN
    
    		  SET @SQL = 'CREATE TRIGGER [' + @Schema + '].[' + @Table + '_SetLastUpdated]
    				ON [' + @Schema + '].[' + @Table + ']
    				    AFTER UPDATE
    				AS
    				BEGIN
    				    IF NOT UPDATE(LastUpdated)
    					   BEGIN
    						  UPDATE t
    						  SET t.LastUpdated = CURRENT_TIMESTAMP
    							 FROM [' + @Schema + '].[' + @Table + '] AS t
    							 INNER JOIN inserted AS i
    							 ON t.' + @Table + 'ID = i.' + @Table + 'ID;
    					   END
    				END'
    
    		  EXEC sp_ExecuteSql @SQL
    
    		  FETCH next FROM c_LastUpdatedTables INTO @Schema, @Table
    	   END
    
        CLOSE c_LastUpdatedTables
        DEALLOCATE c_LastUpdatedTables
    
    END

    Friday, October 31, 2014 6:41 PM