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; 
