Trigger Based Change Data capture

,

Description:

SQL Server has in-built methods to capture changes occurring to tables. Eg. Change data Capture, Change tracking and auditing. However, they have some shortcomings. Such as, CDC does not provide "user" who made the change or Change tracking does not provide before and after image of data, etc.

This trigger based solution provides simple way to capture changes occuring to table and its designed t ocapture changes from all tables to single log table. The change data is logged in JSON format.

Sample json payload for reference:-

Snapshot of Changelog table:-

How to use:

The script can be used to create triggers on mulitple tables by providing comma seperated list which need to be setup in "configuration" section of the script along with schema name. The script has to be run against intended database.

/*
Author: Ankush Parab
Create Date: 2017-12-01
*/

-- Create change log table 
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'ChangeLog')
CREATE TABLE [dbo].[ChangeLog](
	[logid] [BIGINT] IDENTITY(1,1) NOT NULL,
	[LogDate] [DATETIME2](7) NULL,
	[Operation] [VARCHAR](1) NULL,
	[ChangeUser] [VARCHAR](32) NULL,
	[TableName] [VARCHAR](32) NULL,
	[PayLoad] [VARCHAR](MAX) NULL,
	[Processed] BIT DEFAULT 0	
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO



DECLARE @tableList VARCHAR(max)
DECLARE @tableName nVARCHAR(32)
DECLARE @sqlCommand nVARCHAR(MAX) 
DECLARE @schema nVARCHAR(32)


/*---------------------------------------------
					Configuration
----------------------------------------------*/

SET @tableList = 'UCAIREP,UCBGREP'
SET @schema = 'C05DB'
--SET @tableName = 'UCAIREP'
-- create delete trigger

DECLARE cur_tables CURSOR
FOR SELECT * 
FROM STRING_SPLIT(@tableList, ',') 

OPEN cur_tables

FETCH NEXT FROM cur_tables
INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN 
PRINT	'processing ' + @tableName
	SET @sqlCommand =   CAST('' as nVarChar(MAX)) +
	'CREATE OR ALTER TRIGGER '+@schema+'.trg_d_'+@tableName+'
	ON '+@schema+'.'+@tableName+' 
	WITH EXECUTE AS CALLER
	AFTER DELETE
	NOT FOR REPLICATION AS
	BEGIN
		SET NOCOUNT ON
		--PRINT ''1. Entered delete trigger''
		INSERT INTO dbo.ChangeLog (
			LogDate
			, Operation
			, ChangeUser
			, TableName
			, PayLoad
			, Processed
		)
		SELECT SYSDATETIME() -- LogDate - datetime2(7)
			, ''D''           -- Operation - varchar(1)
			, SYSTEM_USER  -- ChangeUser - varchar(32)
			, '''+@tableName+'''     -- TableName - varchar(32)
			, (
				SELECT SYSDATETIME() AS [utcDateTime]
				, CURRENT_TRANSACTION_ID() AS [transactionId]
				, ''D'' AS [operation]
				, SYSTEM_USER AS [userId]
				, '''+@tableName+''' AS [tableName]
				, '''+@schema+''' AS [schema]
				, (
				SELECT COL_NAME(ic.object_id, ic.column_id) AS [Name]
					, t.name AS dataType
					, c.max_length AS [length]
					, c.precision AS [precision]
					, c.scale AS [scale]
				FROM sys.indexes AS i
				INNER JOIN sys.index_columns AS ic
					ON i.object_id = ic.object_id
					AND i.index_id = ic.index_id
				INNER JOIN sys.columns c
					ON c.column_id = ic.column_id
					AND c.object_id = ic.object_id
				INNER JOIN sys.types t
					ON t.system_type_id = c.system_type_id
				WHERE i.is_primary_key = 1
				AND OBJECT_NAME(ic.object_id) = '''+@tableName+'''
				ORDER BY ic.index_column_id
				FOR JSON PATH
				) [uniqueKey]
				, (
				SELECT c.name AS [name]
				, CASE c.name '+
				(SELECT STUFF( (SELECT 
				'  WHEN ''' + c.name+ ''' THEN CAST(d.'+c.name+' AS VARCHAR(MAX)) ' 
				FROM sys.columns c
				INNER JOIN sys.types t
				ON t.system_type_id = c.system_type_id
				WHERE OBJECT_NAME(object_id) = ''+@tableName+''
				FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')

				)
				+'
				END AS [value.old]
				, t.name AS dataType
				, c.max_length AS [length]
				, c.precision AS [precision]
				, c.scale AS [scale]
				FROM sys.columns c
				INNER JOIN sys.types t
				ON t.system_type_id = c.system_type_id
				WHERE OBJECT_NAME(object_id) = '''+@tableName+'''

				ORDER BY column_id
				FOR JSON PATH
				) [columns]
				, NULL as [columnsUpdated]
				FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
				) as Payload
			, 0

		FROM  Deleted d
	END;
	'
	--PRINT @sqlCommand
	EXEC sp_executesql @sqlCommand

	-- create insert trigger

	SET @sqlCommand =   CAST('' as nVarChar(MAX)) +
	'CREATE OR ALTER TRIGGER '+@schema+'.trg_i_'+@tableName+'
	ON '+@schema+'.'+@tableName+' 
	WITH EXECUTE AS CALLER
	AFTER INSERT
	NOT FOR REPLICATION AS
	BEGIN
		SET NOCOUNT ON
		--PRINT ''1. Entered delete trigger''
		INSERT INTO dbo.ChangeLog (
			LogDate
			, Operation
			, ChangeUser
			, TableName
			, PayLoad
			, Processed
		)
		SELECT SYSDATETIME() -- LogDate - datetime2(7)
			, ''I''           -- Operation - varchar(1)
			, SYSTEM_USER  -- ChangeUser - varchar(32)
			, '''+@tableName+'''     -- TableName - varchar(32)
			, (
			SELECT SYSDATETIME() AS [utcDateTime]
			, CURRENT_TRANSACTION_ID() AS [transactionId]
			, ''I'' AS [operation]
			, SYSTEM_USER AS [userId]
			, '''+@tableName+''' AS [tableName]
			, '''+@schema+''' AS [schema]
			, (
			SELECT COL_NAME(ic.object_id, ic.column_id) AS [Name]
				, t.name AS dataType
				, c.max_length AS [length]
				, c.precision AS [precision]
				, c.scale AS [scale]
			FROM sys.indexes AS i
			INNER JOIN sys.index_columns AS ic
				ON i.object_id = ic.object_id
				AND i.index_id = ic.index_id
			INNER JOIN sys.columns c
				ON c.column_id = ic.column_id
				AND c.object_id = ic.object_id
			INNER JOIN sys.types t
				ON t.system_type_id = c.system_type_id
			WHERE i.is_primary_key = 1
			AND OBJECT_NAME(ic.object_id) = '''+@tableName+'''
			ORDER BY ic.index_column_id
			FOR JSON PATH
			) [uniqueKey]
			, (
			SELECT c.name AS [name]
			, CASE c.name '+
			(SELECT STUFF( (SELECT 
			'  WHEN ''' + c.name+ ''' THEN CAST(i.'+c.name+' AS VARCHAR(MAX)) ' 
			FROM sys.columns c
			INNER JOIN sys.types t
			ON t.system_type_id = c.system_type_id
			WHERE OBJECT_NAME(object_id) = ''+@tableName+''
			FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')

			)
			+'
			END AS [value.new]
			, t.name AS dataType
			, c.max_length AS [length]
			, c.precision AS [precision]
			, c.scale AS [scale]
			FROM sys.columns c
			INNER JOIN sys.types t
			ON t.system_type_id = c.system_type_id
			WHERE OBJECT_NAME(object_id) = '''+@tableName+'''

			ORDER BY column_id
			FOR JSON PATH
			) [columns]
			, NULL as [columnsUpdated]
			FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
		) as payload
		, 0 as processed
		FROM  Inserted i
	END;
	'
	--PRINT @sqlCommand
	EXEC sp_executesql @sqlCommand



	-- create update trigger

	SET @sqlCommand =   CAST('' as nVarChar(MAX)) +
	'CREATE OR ALTER TRIGGER '+@schema+'.trg_u_'+@tableName+'
	ON '+@schema+'.'+@tableName+' 
	WITH EXECUTE AS CALLER
	AFTER UPDATE
	NOT FOR REPLICATION AS
	BEGIN
		SET NOCOUNT ON
		--PRINT ''1. Entered delete trigger''
		INSERT INTO dbo.ChangeLog (
			LogDate
			, Operation
			, ChangeUser
			, TableName
			, PayLoad
			, Processed
		)
		SELECT SYSDATETIME() -- LogDate - datetime2(7)
			, ''U''           -- Operation - varchar(1)
			, SYSTEM_USER  -- ChangeUser - varchar(32)
			, '''+@tableName+'''     -- TableName - varchar(32)
			, (
			SELECT SYSDATETIME() AS [utcDateTime]
			, CURRENT_TRANSACTION_ID() AS [transactionId]
			, ''U'' AS [operation]
			, SYSTEM_USER AS [userId]
			, '''+@tableName+''' AS [tableName]
			, '''+@schema+''' AS [schema]
			, (
			SELECT COL_NAME(ic.object_id, ic.column_id) AS [Name]
				, t.name AS dataType
				, c.max_length AS [length]
				, c.precision AS [precision]
				, c.scale AS [scale]
			FROM sys.indexes AS i
			INNER JOIN sys.index_columns AS ic
				ON i.object_id = ic.object_id
				AND i.index_id = ic.index_id
			INNER JOIN sys.columns c
				ON c.column_id = ic.column_id
				AND c.object_id = ic.object_id
			INNER JOIN sys.types t
				ON t.system_type_id = c.system_type_id
			WHERE i.is_primary_key = 1
			AND OBJECT_NAME(ic.object_id) = '''+@tableName+'''
			ORDER BY ic.index_column_id
			FOR JSON PATH
			) [uniqueKey]
			, (
			SELECT c.name AS [name]
			, CASE c.name '+
			(SELECT STUFF( (SELECT 
			'  WHEN ''' + c.name+ ''' THEN CAST(d.'+c.name+' AS VARCHAR(MAX)) ' 
			FROM sys.columns c
			INNER JOIN sys.types t
			ON t.system_type_id = c.system_type_id
			WHERE OBJECT_NAME(object_id) = ''+@tableName+''
			FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')

			)
			+'
			END AS [value.old]
			, CASE c.name '+
			(SELECT STUFF( (SELECT 
			'  WHEN ''' + c.name+ ''' THEN CAST(i.'+c.name+' AS VARCHAR(MAX)) ' 
			FROM sys.columns c
			INNER JOIN sys.types t
			ON t.system_type_id = c.system_type_id
			WHERE OBJECT_NAME(object_id) = ''+@tableName+''
			FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')

			)
			+'
			END AS [value.new]
			, t.name AS dataType
			, c.max_length AS [length]
			, c.precision AS [precision]
			, c.scale AS [scale]
			FROM sys.columns c
			INNER JOIN sys.types t
			ON t.system_type_id = c.system_type_id
			WHERE OBJECT_NAME(object_id) = '''+@tableName+'''

			ORDER BY column_id
			FOR JSON PATH
			) [columns]
			, ( SELECT name
				FROM sys.columns  
				WHERE OBJECT_NAME(object_id) = '''+@tableName+'''
				AND (SUBSTRING( COLUMNS_UPDATED(), (column_id / 8) +1,1) & power(2, (((column_id - 1 ) % 8) + 1) - 1)) > 0
				ORDER BY column_id
				FOR JSON PATH
				) as [columnsUpdated]
			FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
		) as Payload
		, 0 as processed

		FROM Inserted i
		JOIN Deleted d
			ON ' +
			 (SELECT STUFF( (
			SELECT ' AND  i.' + COL_NAME(ic.object_id, ic.column_id)  + ' = d.' + COL_NAME(ic.object_id, ic.column_id)   AS [Name]
			FROM sys.indexes AS i
			INNER JOIN sys.index_columns AS ic
				ON i.object_id = ic.object_id
				AND i.index_id = ic.index_id
			INNER JOIN sys.columns c
				ON c.column_id = ic.column_id
				AND c.object_id = ic.object_id
			INNER JOIN sys.types t
				ON t.system_type_id = c.system_type_id
			WHERE i.is_primary_key = 1
			AND OBJECT_NAME(ic.object_id) = ''+@tableName+''
			ORDER BY ic.index_column_id
	FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 4, ''))
			+';
	END;
	'
	--PRINT @sqlCommand
	EXEC sp_executesql @sqlCommand

	FETCH NEXT FROM cur_tables
	INTO @tableName
END
CLOSE cur_tables;  
DEALLOCATE cur_tables;

Rate

1 (3)

Share

Share

Rate

1 (3)