Creating a "date updated" column in MS SQL?

Question

Is there any simple way to create a column in MS SQL that will track the last time a record was updated?

I would like to have two fields. One to track when the record was created. That one is simple. Create a datetime field and set its default to getdate(). However the second field seams to be a bit more tricky. I want it to have the latest date (and time) the record was modified.

My options are:

• Include getdate() in every update statement - not an option, these tables will be accessed from MS Access
• Allow updates only through an SP. - not an option, these tables will be accessed from MS Access
• Create triggers for each table - the DB is recreated on many machines and I am afraid it will conflict or be forgotten or get out of synch.

Are there any other options?

Answer 1

Triggers are pretty much your only option here. What is to stop anyone from updating tables with SSMS, those updates would not update the date updated column in that case

Answer 2

Stored procs are an option with Access, but you have to intercept the event in VBA and call a stored proc instead, followed by Me.Undo.

I've done it but many moons ago and don't have sample code to show you, sorry.

Otherwise triggers are the usual way, either before or after ones.

Answer 3

Option 4:

Create a stored procedure that automatically creates triggers for all the tables in your database. In SQL 2005, optionally run this trigger any time any table is created (using a DDL trigger).

CREATE PROC UpdateTriggersCreate
AS
DECLARE
   @TableSchema sysname,
   @TableName sysname,
   @PrimaryKeys nvarchar(4000),
   @ObjectName nvarchar(4000)
   @TriggerName nvarchar(4000),
   @SQL nvarchar(4000);
SET @TableName = '';
SET @TableSchema = '';
WHILE 1 = 1 BEGIN
   SELECT TOP 1
      @TableSchema = TABLE_SCHEMA,
      @TableName = TABLE_NAME
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE
      COLUMN_NAME = 'LastUpdatedDate'
      AND (
         TABLE_SCHEMA > @TableSchema
         OR (
            TABLE_SCHEMA = @TableSchema
            AND TABLE_NAME > @TableName
         )
      )
   ORDER BY TABLE_SCHEMA, TABLE_NAME;
   IF @@RowCount = 0 BREAK;

   IF NOT EXISTS (
      SELECT *
      FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
      WHERE
         C.CONSTRAINT_TYPE = 'PRIMARY KEY'
         AND C.TABLE_SCHEMA = @TableSchema
         AND C.TABLE_NAME = @TableName
   ) BEGIN
       PRINT '-- Not processing table ''' + @TableSchema + '.' + @TableName + ''' because automatic last updated triggers cannot be used on tables with no primary key.';
       CONTINUE;
   END;

   SET @PrimaryKeys = NULL;
   SELECT @PrimaryKeys = Coalesce(@PrimaryKeys + ' AND T.', 'T.') + QuoteName(Y.COLUMN_NAME) + ' = I.' + QuoteName(Y.COLUMN_NAME)
   FROM
      INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
      INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE Y
         ON T.CONSTRAINT_CATALOG = Y.CONSTRAINT_CATALOG
         AND T.CONSTRAINT_SCHEMA = Y.CONSTRAINT_SCHEMA
         AND T.CONSTRAINT_NAME = Y.CONSTRAINT_NAME
   WHERE
      T.CONSTRAINT_TYPE = 'PRIMARY KEY'
      AND T.TABLE_SCHEMA = @TableSchema
      AND T.TABLE_NAME = @TableName;
   -- order is not important which is good because ORDER BY is unreliable in this case

   SET @ObjectName = @TableSchema + '.' + @TableName;
   SET @TriggerName = 'TR_' + Replace(@ObjectName, '.', '_') + '_U_TimeUpdated';
   SET @SQL = 'IF Object_ID(''' + @TriggerName + ''', ''TR'') IS NOT NULL DROP TRIGGER ' + @TriggerName;
   EXEC sp_executesql @SQL;
   SET @SQL = 'CREATE TRIGGER ' + @TriggerName + ' ON ' + @ObjectName + ' FOR INSERT
AS
SET NOCOUNT ON
UPDATE T
SET T.LastUpdatedDate = GetDate()
FROM
   ' + @ObjectName + ' T
   INNER JOIN Inserted I ON ' + @PrimaryKeys;

   EXEC sp_executesql @SQL;
END;

Once you have a stored procedure like this, schedule it to run once a day or (in sql 2005 and up) in response to the DDL creation of tables.

Update 1

The code now handles schema properly, and looks up the primary keys. It also reports on and skips tables that have no primary key.

I'm not sure if I worked out all the syntax errors--I adapted it from code I've done this in before and didn't actually test it. I'm sure you can figure it out.