SQL Server - Generate triggers for your "LastModified" columns with a fancy SQL script!

Every table in our database should have a "LastModified" column to record the last modified time of each row.

Figure: Sample table with a LastModified column

Our rule "Do you have standard Tables and Columns?" says the column should be called "DateModified" which has the same purpose.

How do you populate that field?

#1
Either in your data access layer by setting the field manually

        public Auction SetAuctionStatus(long auctionID, short status)
        {
            using (var dc = new AuctionEntities())
            {

                var auction = from auctionFromDB in dc.Auctions
                                  where auctionFromDB.AuctionID == auctionID
                                  select auctionFromDB;

                var singleAuction = auction.SingleOrDefault();
                if (singleAuction != null)
                {
                    singleAuction.AuctionStatus = status;
                    singleAuction.LastModified = DateTime.Now;
                    dc.SaveChanges();
                }

                return singleAuction;
            }
        }

 Figure: Save an auction object to the database, but don't forget to set LastModified

 

#2
You create a trigger for those columns

CREATE TRIGGER [dbo].[trgAuctionsLastModifiedDate] ON [dbo].[Auctions]
FOR UPDATE 
AS
UPDATE [Auctions] SET [Auctions].LastModified=getdate()
FROM [Auctions] INNER JOIN Inserted ON [Auctions].[AuctionID]= Inserted.[AuctionID]
GO

Figure: A trigger that updates LastModified on UPDATE

No one wants to write those triggers manually so here it is:

A passive code generator script that generates a rough trigger template that needs some slight fixes

• A passive code generator requires some human intervention to implement code ;-)
• It generates code and after that it is under your responsibility

 

Run this script on your database and it will spit out the above "CREATE TRIGGER" statement for all tables with the column "LastModified"

-- get tables with LastModified column 
	--select o.Name
	--from sys.objects o INNER JOIN  sys.columns c ON o.Object_ID = c.Object_ID
	--where o.type = 'U'
	--and c.Name = N'LastModified' 
	--order by 1


-- check if trigger already exists
	--Select * from sysObjects
	--Where type ='Tr' 
	--	and name NOT LIKE 'LastModified'

-- generate SQL script that needs some fixes...

DECLARE @tableName VARCHAR(50)
DECLARE @messageHeader VARCHAR(250)
DECLARE @sqlString VARCHAR(2000)
DECLARE @primaryKeyString VARCHAR(2000)
DECLARE @pkColumn VARCHAR(20)

DECLARE table_cursor CURSOR FOR 
	SELECT o.Name
	FROM sys.objects o INNER JOIN  sys.columns c ON o.Object_ID = c.Object_ID
	WHERE o.type = 'U'
	AND c.Name = N'LastModified' 
	ORDER BY 1

OPEN table_cursor

FETCH NEXT FROM table_cursor 
INTO @tableName



	WHILE @@FETCH_STATUS = 0
	BEGIN
	   
	   SELECT @messageHeader = '----- Trigger for : ' + @tableName

		-- getting primaryKeyString
		SELECT @primaryKeyString =	''
			
			-- Declare an inner cursor based   
		   
		   DECLARE pk_cursor CURSOR FOR 
		   SELECT c.COLUMN_NAME
			FROM 	INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
					INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
				WHERE 	pk.TABLE_NAME = @TableName
				AND	CONSTRAINT_TYPE = 'PRIMARY KEY'
				AND	c.TABLE_NAME = pk.TABLE_NAME
				AND	c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

		   OPEN pk_cursor
		   FETCH NEXT FROM pk_cursor INTO @pkColumn

		   IF @@FETCH_STATUS <> 0 
			  PRINT '<>'     


		   WHILE @@FETCH_STATUS = 0
		   BEGIN
	      
			  
			  SELECT @primaryKeyString = @primaryKeyString + REPLACE('
			  AND [TableName].[UniqueID]= Inserted.[UniqueID]','UniqueID', @pkColumn)
			  
			  FETCH NEXT FROM pk_cursor INTO @pkColumn
		   
		   END

		   CLOSE pk_cursor
		   DEALLOCATE pk_cursor
	 

		   SELECT @sqlString = REPLACE('CREATE TRIGGER trgTableNameLastModifiedDate ON [TableName]
											FOR UPDATE 
											AS
											UPDATE [TableName] SET [TableName].LastModified=getdate()
											FROM [TableName] INNER JOIN Inserted ON PrimaryKeys
											GO

											','PrimaryKeys', @primaryKeyString)

		   SELECT @sqlString = REPLACE(@sqlString,'TableName', @tableName)
	 
		   
		   PRINT @messageHeader
		   PRINT @sqlString
   
   FETCH NEXT FROM table_cursor 
   INTO @tableName
END

CLOSE table_cursor
DEALLOCATE table_cursor
GO
  

Figure: SQL script that generates trigger scripts for you 

 

PS
At SSW have a tool called SQLAuditor that audits your database and helps you create the LastModified columns with a nice wizard.