How to make log using trigger?

Question

Dear all

I have a database that in this database I have to record log of any changes in each table. To make this I designed 3 Tables. 1) TableMasterFile that contains list of all tables valid in Database 2)DataIdentityTable that save all Primarykeys value for each table 3)log that it save changes of any record for each table.

Then I added a trigger on each table that will run for Insert, Update, Delete and it will save changes in the log table. Everything work correctly but problem is that if I active the log trigger it will make insert happen 12 times slower! So for an insertion that have to insert around 60 records it will take almost 2 minutes to insert and it is so slow.

I put a trigger of insert so you can see how I made the trigger.

Question is this: Is there anyway to make it faster?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER TRIGGER [dbo].[GenerationOfBillingASIALog] 
   ON  [dbo].[GenerationOfBillingASIA] 
   AFTER INSERT
AS 
BEGIN
	
	SET NOCOUNT ON;


	SELECT * INTO GenerationOfBillingASIAIns FROM INSERTED
	SELECT * INTO GenerationOfBillingASIADel FROM DELETED
	

    DECLARE @SQLQuery VARCHAR(Max)
	DECLARE @PrimaryKeyID INT
	DECLARE @PKColumn VARCHAR(250)
	DECLARE	@TableName VARCHAR(128)
	DECLARE @ColumnTable TABLE(ColumnName VARCHAR(250), Counter INT identity)
	DECLARE @RowCounts AS INT
	DECLARE @ColumnName AS VARCHAR(250)
	DECLARE @i AS INT
	
	-- Name of the table that will use this Trigger.
	SET @TableName = 'GenerationOfBilling'
	
	-- This will get the Primarykey column of the table used.
	SET @PKColumn = (SELECT TOP 1 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)
	
	-- This will insert all columns of the table used in this trigger in the temp table for the Columns
	INSERT INTO @ColumnTable
	SELECT column_name 'ColumnName'
	FROM information_schema.columns
	WHERE table_name = @TableName + 'Ins'
	

	SET @SqlQuery = 'INSERT INTO DataIdentityTable
	SELECT * FROM (
	SELECT COALESCE(TMF.TableID,0) TableID, CAST(TI.[' + @PKColumn + '] AS VARCHAR) AS PrimaryKey FROM GenerationOfBillingASIAIns TI LEFT OUTER JOIN TableMasterFile TMF ON TMF.TableName = ''' + @TableName + '''
	UNION
	SELECT COALESCE(TMF.TableID,0) TableID, CAST(TD.[' + @PKColumn + '] AS VARCHAR) AS PrimaryKey FROM GenerationOfBillingASIADel TD LEFT OUTER JOIN TableMasterFile TMF ON TMF.TableName = ''' + @TableName + '''
	) GenerationOfBillingASIA WHERE CAST(TableID AS VARCHAR) + ''' + 'SEPARATOR' + ''' + CAST(PrimaryKey AS VARCHAR) 
	NOT IN (SELECT CAST(TableID AS VARCHAR) + ''' + 'SEPARATOR' + ''' + CAST(PrimaryKey AS VARCHAR) FROM DataIdentityTable)'
	EXECUTE(@SqlQuery)

	-- INSERT --
	ELSE IF (SELECT COUNT(INSERTED.InvoiceNumber) FROM INSERTED) > 0
	BEGIN	
		-- This will check each column of the table used and Save all deleted values in each column into the Log table
		-- along with the information needed. (tableID, columnName, primarykey, userID, date, etc...)
		SET @i = 0
		SET @RowCounts = (SELECT COUNT(*) AS COUNT FROM @ColumnTable)
		While @i < @RowCounts
		BEGIN
		SET @i = @i+1
		SET @ColumnName = (SELECT Top 1 ColumnName FROM @ColumnTable WHERE Counter = @i)
		
		SET @SQLQuery = 'INSERT INTO Log 
				SELECT PK.PrimaryKeyID, '''+@ColumnName+''' AS [ColumnName],
				NULL AS OldValue, CAST(TI.['+@ColumnName+'] AS VARCHAR(50)) AS NewValue, 
				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM GenerationOfBillingASIAIns TI INNER JOIN 
				(SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C LEFT OUTER JOIN TableMasterFile TMF 
				ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = ''' + @TableName + ''' + CAST(TI.[' + @PKColumn + '] AS VARCHAR)'
				
		EXECUTE(@SQLQuery)
		END
	END


	-- This will drop the physical copy of Inserted and Deleted tables created above.
	DROP TABLE GenerationOfBillingASIAIns
	DROP TABLE GenerationOfBillingASIADel
END

---

Nothing is Impossible

Answer 1

The use of dynamic SQL should be the source of your problem. Don't use it, it is not necessary: you know all facts, like primary key columns, when creating the trigger.

Use the dynamic SQL to emit the trigger creation T-SQL code.

Also don't create copies of DELETED and INSERTED, especially when you continue using both, the copy and the original tables.

Also DELETED and its copy are not necessary in a AFTER INSERT trigger.

You trigger code also contains an error: The ELSE before the IF. Use copy and past for posting code. Also ensure, when possible, that your code is correct and can be execute, especially when you say it works.

When I read your code correctly, then your  logging all columns when the row is affected by an insert. In this case you should consider using a simple copy logging with a log table per tracked table, e.g.:

ALTER TRIGGER [dbo].[GenerationOfBillingASIALog] ON [dbo].[GenerationOfBillingASIA]
    AFTER INSERT
AS
    SET NOCOUNT ON ;

    INSERT  INTO [Log].[GenerationOfBillingASIA]
            ( ChangeType ,
              <column_list> 
            )
            SELECT  'I' ,
                    <column_list>
            INTO    GenerationOfBillingASIAIns
            FROM    INSERTED ;

Answer 2

Dear Stefan

Thank you so much for your fast reply. Actually I have only one log table to save all changes. bellow you can see the design of my log. My database has more than 200 Tables therefore I made one trigger to be used for all tables.

The idea is that in log table I will save what column had change and how was the change.

TableMasterFile:

TableID(PK, int, not null), TableName(varchar(100), not null), CreatedDate(datetime, not null)

DataIdentityTable:

PrimaryKeyID (PK, int, not null), TableID (FK, int, not null), PrimaryKeyValue(varchar(25), not null)

log:

LogID(PK, int, not null), PrimaryKeyID(FK, int, not null) ColumnName(varchar(50), not null), OldValue(varchar(max), null), NewValue(varchar(max), null), Type(smallint, not null) UserID(varchar(20), not null), Date(datetime, not null)

as you can see in Log table I save the name of the column the old and new value and the type of change that if it was insert, update or delete.

---

Nothing is Impossible

Answer 3

Here are some pointers we can try of

1. Avoid using Information Schema tables. Instead you can use sp_pkeys or use other system tables to get the primay key columns

2. Modify the IF ELSE part with the below code. This code will be construct all the Insert statements in one shot and execute. Rather than running a loop.

		SELECT @SQLQuery = @SQLQuery + CHAR(13) + 'INSERT INTO Log 
				SELECT PK.PrimaryKeyID, '''+ C.ColumnName +''' AS [ColumnName],
				NULL AS OldValue, CAST(TI.['+C.ColumnName +'] AS VARCHAR(50)) AS NewValue, 
				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM GenerationOfBillingASIAIns TI INNER JOIN 
				(SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C LEFT OUTER JOIN TableMasterFile TMF 
				ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = ''' + @TableName + ''' + CAST(TI.[' + @PKColumn + '] AS VARCHAR)' + CHAR(13)
		  FROM @ColumnTable C
		EXECUTE(@SQLQuery)

Note : The logic you have written may fail for the table with composite primary keys.

Answer 4

Actually I have only one log table to save all changes. bellow you can see the design of my log. My database has more than 200 Tables therefore I made one trigger to be used for all tables.

Imho this concept is not tenable, when you want to optimize performance.. To be precise: One log table may fit your problem, but a generic trigger using dynamic SQL will not.

Answer 5

Dear Stefan

I changed the query to the query bellow but it still take such a long time to insert! almost same as old one!

Anyway Thank you so much

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER [dbo].[GenerationOfBillingASIALog2] 
   ON  [dbo].[GenerationOfBillingASIA] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE	@TableName VARCHAR(128)
	DECLARE	@PrimaryKey VARCHAR(128)
	DECLARE	@TableID VARCHAR(128)
	-- Name of the table that will use this Trigger.
	SET @TableName = 'GenerationOfBillingASIA'
	--INSERT INTO DataIdentityTable (TableID and PrimaryKey)
	SET @TableID = (Select Top 1 TableID FROM TableMasterFile Where TableName = @TableName)
	INSERT INTO DataIdentityTable SELECT @TableID as [TableID], InvoiceNumber as PrimaryKey From INSERTED
	--INSERT INTO LOG
	INSERT INTO Log 
				SELECT PK.PrimaryKeyID, 'InvoiceNumber' AS [ColumnName],
				'NULL' AS OldValue, CAST(TI.[InvoiceNumber] AS VARCHAR(50)) AS NewValue, 
				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.[InvoiceNumber] AS VARCHAR)
	INSERT INTO Log 
				SELECT PK.PrimaryKeyID, 'BillingDate' AS [ColumnName],
				'NULL' AS OldValue, CAST(TI.BillingDate AS VARCHAR(50)) AS NewValue, 
				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.BillingDate AS VARCHAR)
	INSERT INTO Log 
				SELECT PK.PrimaryKeyID, 'DueDate' AS [ColumnName],
				'NULL' AS OldValue, CAST(TI.DueDate AS VARCHAR(50)) AS NewValue, 
				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.DueDate AS VARCHAR)
				
	INSERT INTO Log 
				SELECT PK.PrimaryKeyID, 'Particular' AS [ColumnName],
				'NULL' AS OldValue, CAST(TI.Particular AS VARCHAR(50)) AS NewValue, 
				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.Particular AS VARCHAR)
				
	INSERT INTO Log 
				SELECT PK.PrimaryKeyID, 'PrincipalAmount' AS [ColumnName],
				'NULL' AS OldValue, CAST(TI.PrincipalAmount AS VARCHAR(50)) AS NewValue, 
				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.PrincipalAmount AS VARCHAR)
				
	INSERT INTO Log 
				SELECT PK.PrimaryKeyID, 'RestructuredAmount' AS [ColumnName],
				'NULL' AS OldValue, CAST(TI.RestructuredAmount AS VARCHAR(50)) AS NewValue, 
				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.RestructuredAmount AS VARCHAR)
				
	INSERT INTO Log 
				SELECT PK.PrimaryKeyID, 'ReferencePenalty' AS [ColumnName],
				'NULL' AS OldValue, CAST(TI.ReferencePenalty AS VARCHAR(50)) AS NewValue, 
				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.ReferencePenalty AS VARCHAR)
				
	INSERT INTO Log 
				SELECT PK.PrimaryKeyID, 'Balance' AS [ColumnName],
				'NULL' AS OldValue, CAST(TI.Balance AS VARCHAR(50)) AS NewValue, 
				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.Balance AS VARCHAR)
				
	INSERT INTO Log 
				SELECT PK.PrimaryKeyID, 'BStatus' AS [ColumnName],
				'NULL' AS OldValue, CAST(TI.BStatus AS VARCHAR(50)) AS NewValue, 
				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.BStatus AS VARCHAR)
				
	INSERT INTO Log 
				SELECT PK.PrimaryKeyID, 'PostingDate' AS [ColumnName],
				'NULL' AS OldValue, CAST(TI.PostingDate AS VARCHAR(50)) AS NewValue, 
				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.PostingDate AS VARCHAR)
				
	INSERT INTO Log 
				SELECT PK.PrimaryKeyID, 'SourceMode' AS [ColumnName],
				'NULL' AS OldValue, CAST(TI.SourceMode AS VARCHAR(50)) AS NewValue, 
				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.SourceMode AS VARCHAR)
				
	INSERT INTO Log 
				SELECT PK.PrimaryKeyID, 'ReferenceRestructuring' AS [ColumnName],
				'NULL' AS OldValue, CAST(TI.ReferenceRestructuring AS VARCHAR(50)) AS NewValue, 
				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.ReferenceRestructuring AS VARCHAR)
				
	INSERT INTO Log 
				SELECT PK.PrimaryKeyID, 'TransactionCode' AS [ColumnName],
				'NULL' AS OldValue, CAST(TI.TransactionCode AS VARCHAR(50)) AS NewValue, 
				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.TransactionCode AS VARCHAR)
				
	INSERT INTO Log 
				SELECT PK.PrimaryKeyID, 'InterestAmount' AS [ColumnName],
				'NULL' AS OldValue, CAST(TI.InterestAmount AS VARCHAR(50)) AS NewValue, 
				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.InterestAmount AS VARCHAR)
				

END
GO

---

Nothing is Impossible

Answer 6

Thank you so much Sorna

I tried your code but it didn't change the speed. :)

---

Nothing is Impossible

Answer 7

A question first: What is the DataIdentityTable for?

The log insertion should be happen in only one SQL statement, something like:

DECLARE @LogHeader TABLE ( ID INT ) ;
DECLARE @LogHeaderID INT ;
	
INSERT  INTO LogHeader
        ( [Type], [UserID], [Date] )
OUTPUT  INSERTED.ID
        INTO @LogHeader
VALUES  ( 0, SYSTEM_USER, GETDATE() ) ;                    

SELECT  @LogHeaderID = ID
FROM    @LogHeader ;
                  
INSERT  INTO LogDetail
        ( LogHeaderID ,
          ColumnName ,
          OldValue ,
          NewValue 
        )
        SELECT  @LogHeaderID ,
                'BillingDate' ,
                NULL ,
                CAST(BillingDate AS NVARCHAR(MAX))
        FROM    INSERTED 
        UNION ALL
        SELECT  @LogHeaderID ,
                'DueDate' ,
                NULL ,
                CAST(DueDate AS NVARCHAR(MAX))
        FROM    INSERTED 
        UNION ALL
        ... ;   

Answer 8

Some of my PrimaryKeys are varchar and some up to 50 char! so reduce space usage I put the primarykey in DataIdentityTable so that instead of those varchar i use an integer and save the space :)

Anyway I did the same as you said but I mixed it with the answer of Soran and I got the fast query that can insert in less than one second :)

Also may I ask to explain this part of your code? I am not pro to understand it well :)

DECLARE @LogHeader TABLE ( ID INT ) ;
DECLARE @LogHeaderID INT ;
	
INSERT  INTO LogHeader
        ( [Type], [UserID], [Date] )
OUTPUT  INSERTED.ID
        INTO @LogHeader
VALUES  ( 0, SYSTEM_USER, GETDATE() ) ;                    

SELECT  @LogHeaderID = ID
FROM    @LogHeader ;

Thank you so much for your help

---

Nothing is Impossible

Answer 9

This is what I used:

SET @SQLQuery = ''
SELECT @SQLQuery = @SQLQuery + 'UNION' +CHAR(13) + 'SELECT TI.['+ @PKColumn +'], '''+ C.ColumnName +''' AS [ColumnName],
				NULL AS OldValue, CAST(TI.['+C.ColumnName +'] AS VARCHAR(50)) AS NewValue, 
				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM GenerationOfBillingASIAIns TI ' + CHAR(13)
		  FROM @ColumnTable C
Set @SQLQuery = SUBSTRING(@SQLQuery, 6, Len(@SQLQuery))
SET @SQLQuery = 'INSERT INTO [LOG]
			Select PK.PrimaryKeyID, [ColumnName], OldValue, NewValue, [Type], [UserID], [Date] from (' + @SQLQuery
SET @SQLQuery = @SQLQuery + ') A INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = ''' + @TableName + ''' + CAST(A.[' + @PKColumn + '] AS VARCHAR)'

---

Nothing is Impossible

Answer 10

This is just the generic table layout for a column based storage in a log table.

You need a master table which holds the information when, by-whom and what (kind of operation) occurred. This is my LogHeader table. The column values are stored in a detail table, here called LogDetail. btw, in my example there is the primary key column missing in that table.

The way you're reading the inserted row and its new ID is error prone to parallel processing.