/*
AUTHOR : BRAHMANAND SHUKLA
DATE : 18-MAY-2018
PURPOSE : Generic trigger for maintaining the Audit Log
STEPS FOR IMPLEMENTATION:
STEP 1: Create the following tables on the same database or on seperate Audit Log Database.
I have used seperate database for Audit Log with name "Audit_Log".
CREATE TABLE [AUDIT_LOG_TABLE_PRIMARY_KEY]
(
[ID] NUMERIC NOT NULL IDENTITY(1, 1) CONSTRAINT PK_AUDIT_LOG_TABLE_PRIMARY_KEY_ID PRIMARY KEY CLUSTERED
, [TABLE_SCHEMA] SYSNAME NOT NULL
, [TABLE_NAME] SYSNAME NOT NULL
, [PRIMARY_KEY_COLUMN_NAME] SYSNAME NOT NULL
, CONSTRAINT UQ_AUDIT_LOG_TABLE_PRIMARY_KEY_TABLE_SCHEMA_TABLE_NAME UNIQUE ([TABLE_SCHEMA], [TABLE_NAME])
)
CREATE TABLE [AUDIT_LOG]
(
[ID] NUMERIC NOT NULL IDENTITY(1, 1)
, [TABLE_NAME] SYSNAME NOT NULL
, [PRIMARY_KEY_COLUMN_NAME] SYSNAME NOT NULL
, [PRIMARY_KEY_COLUMN_VALUE] NVARCHAR(MAX)
, [AUDIT_ACTION] CHAR(1) NOT NULL
, [HOST_NAME] NVARCHAR(100) NOT NULL
, [APP_NAME] NVARCHAR(100) NOT NULL
, [AUDIT_USERID] NVARCHAR(50) NOT NULL
, [AUDIT_DATETIME] DATETIME NOT NULL CONSTRAINT DF_AUDIT_LOG_DATETIME DEFAULT(GETDATE())
, [COLUMN_NAME] SYSNAME NOT NULL
, [COLUMN_OLD_VALUE] NVARCHAR(MAX)
, [COLUMN_NEW_VALUE] NVARCHAR(MAX)
)
STEP 2: Change the table name in the trigger name and on clause with the desired table name.
STEP 3: Change the database name and schema name of the "AUDIT_LOG_TABLE_PRIMARY_KEY" and "AUDIT_LOG" tables in the trigger.
STEP 4: Create the trigger on the desired table.
STEP 5: If your table doesn't has the Primary Key column then add an entry in "AUDIT_LOG_TABLE_PRIMARY_KEY" table.
STEP 6: You are all set. Check "AUDIT_LOG" tables for Audit Logs by performing INSERT, UPDATE and DELETE on the source table.
IMPORTANT NOTE:
1) The trigger will write the Old & New values in "AUDIT_LOG" table in case of UPDATE and only if there is change in value.
2) The trigger will write the columns and their values as New value in "AUDIT_LOG" table in case of INSERT.
3) The trigger will write the columns and their values as Old value in "AUDIT_LOG" table in case of DELETE.
*/
CREATE TRIGGER TR_Audit_Log_tablename
ON tablename
FOR INSERT, UPDATE, DELETE
NOT FOR REPLICATION
AS
BEGIN TRY
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF OBJECT_ID('tempdb..#COLUMNS') IS NOT NULL DROP TABLE #COLUMNS;
CREATE TABLE #COLUMNS
(
[COLUMN_NAME] SYSNAME
, [ORDINAL_POSITION] INT PRIMARY KEY CLUSTERED
)
IF OBJECT_ID('tempdb..#inserted') IS NOT NULL DROP TABLE #inserted;
IF OBJECT_ID('tempdb..#deleted') IS NOT NULL DROP TABLE #deleted;
DECLARE @TABLE_SCHEMA SYSNAME
DECLARE @TABLE_NAME SYSNAME
DECLARE @AUDIT_ACTION CHAR(1)
DECLARE @PRIMARY_KEY_COLUMN_NAME SYSNAME
DECLARE @PRIMARY_KEY_COLUMN_VALUE NVARCHAR(MAX)
DECLARE @PARAMETER_DEFINITION NVARCHAR(MAX)
DECLARE @SQL_QUERY NVARCHAR(MAX)
DECLARE @COLUMN_NAME SYSNAME
DECLARE @ORDINAL_POSITION INT
IF EXISTS(SELECT 1 FROM inserted)
AND EXISTS(SELECT 1 FROM deleted)
BEGIN
SET @AUDIT_ACTION = 'U';
END
ELSE
BEGIN
IF EXISTS (SELECT 1 FROM deleted)
BEGIN
SET @AUDIT_ACTION = 'D';
END
ELSE IF EXISTS(SELECT 1 FROM inserted)
BEGIN
SET @AUDIT_ACTION = 'I';
END
END
IF @AUDIT_ACTION IS NULL RETURN;
SELECT @TABLE_SCHEMA = OBJECT_SCHEMA_NAME(parent_id)
, @TABLE_NAME = OBJECT_NAME(parent_id)
FROM sys.triggers
WHERE object_id = @@PROCID;
-- Get the Primary Key column name
BEGIN
SELECT @PRIMARY_KEY_COLUMN_NAME = KEY_COLUMN_USAGE.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE
ON TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TABLE_CONSTRAINTS.CONSTRAINT_NAME = KEY_COLUMN_USAGE.CONSTRAINT_NAME
WHERE TABLE_CONSTRAINTS.TABLE_SCHEMA = @TABLE_SCHEMA
AND TABLE_CONSTRAINTS.TABLE_NAME = @TABLE_NAME
IF @PRIMARY_KEY_COLUMN_NAME IS NULL
BEGIN
SELECT @PRIMARY_KEY_COLUMN_NAME = [PRIMARY_KEY_COLUMN_NAME]
FROM Audit_Log.dbo.[AUDIT_LOG_TABLE_PRIMARY_KEY]
WHERE [TABLE_SCHEMA] = @TABLE_SCHEMA
AND [TABLE_NAME] = @TABLE_NAME
END
END
SELECT * INTO #inserted FROM inserted;
SELECT * INTO #deleted FROM deleted;
-- Get all the columns of the table
INSERT INTO #COLUMNS (COLUMN_NAME, ORDINAL_POSITION)
SELECT [COLUMN_NAME], [ORDINAL_POSITION]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_SCHEMA] = @TABLE_SCHEMA
AND [TABLE_NAME] = @TABLE_NAME;
-- Iterate through the columns and maintain the Audit Log Detail (Old and New values of the column against the supplied Primary Key).
SET @ORDINAL_POSITION = 1;
WHILE EXISTS (SELECT 1 FROM #COLUMNS WHERE [ORDINAL_POSITION] = @ORDINAL_POSITION)
BEGIN
SELECT @COLUMN_NAME = [COLUMN_NAME]
FROM #COLUMNS
WHERE ORDINAL_POSITION = @ORDINAL_POSITION;
SET @SQL_QUERY = NULL;
IF (@AUDIT_ACTION = 'U')
BEGIN
SET @SQL_QUERY = 'SELECT ''' + @TABLE_NAME + ''' AS [TABLE_NAME]
, ''' + @PRIMARY_KEY_COLUMN_NAME + ''' AS [PRIMARY_KEY_COLUMN_NAME]
, INS.[' + @PRIMARY_KEY_COLUMN_NAME + '] AS [PRIMARY_KEY_COLUMN_VALUE]
, ''' + @AUDIT_ACTION + ''' AS [AUDIT_ACTION]
, HOST_NAME() AS HOST_NAME
, APP_NAME() AS APP_NAME
, SUSER_SNAME() AS AUDIT_USERID
, GETDATE() AS AUDIT_DATETIME
, ''' + @COLUMN_NAME + ''' AS [COLUMN_NAME]
, CAST(DEL.[' + @COLUMN_NAME + '] AS NVARCHAR(MAX)) AS [COLUMN_OLD_VALUE]
, CAST(INS.[' + @COLUMN_NAME + '] AS NVARCHAR(MAX)) AS [COLUMN_NEW_VALUE]
FROM #deleted DEL
INNER JOIN #inserted INS
ON INS.[' + @PRIMARY_KEY_COLUMN_NAME + '] = DEL.[' + @PRIMARY_KEY_COLUMN_NAME + ']
WHERE ISNULL(CAST(DEL.[' + @COLUMN_NAME + '] AS NVARCHAR(MAX)), '''') ISNULL(CAST(INS.[' + @COLUMN_NAME + '] AS NVARCHAR(MAX)), '''')';
END
ELSE IF (@AUDIT_ACTION = 'I')
BEGIN
SET @SQL_QUERY = 'SELECT ''' + @TABLE_NAME + ''' AS [TABLE_NAME]
, ''' + @PRIMARY_KEY_COLUMN_NAME + ''' AS [PRIMARY_KEY_COLUMN_NAME]
, INS.[' + @PRIMARY_KEY_COLUMN_NAME + '] AS [PRIMARY_KEY_COLUMN_VALUE]
, ''' + @AUDIT_ACTION + ''' AS [AUDIT_ACTION]
, HOST_NAME() AS HOST_NAME
, APP_NAME() AS APP_NAME
, SUSER_SNAME() AS AUDIT_USERID
, GETDATE() AS AUDIT_DATETIME
, ''' + @COLUMN_NAME + ''' AS [COLUMN_NAME]
, NULL AS [COLUMN_OLD_VALUE]
, CAST(INS.[' + @COLUMN_NAME + '] AS NVARCHAR(MAX)) AS [COLUMN_NEW_VALUE]
FROM #inserted INS';
END
ELSE IF (@AUDIT_ACTION = 'D')
BEGIN
IF (@COLUMN_NAME = @PRIMARY_KEY_COLUMN_NAME)
BEGIN
SET @SQL_QUERY = 'SELECT ''' + @TABLE_NAME + ''' AS [TABLE_NAME]
, ''' + @PRIMARY_KEY_COLUMN_NAME + ''' AS [PRIMARY_KEY_COLUMN_NAME]
, DEL.[' + @PRIMARY_KEY_COLUMN_NAME + '] AS [PRIMARY_KEY_COLUMN_VALUE]
, ''' + @AUDIT_ACTION + ''' AS [AUDIT_ACTION]
, HOST_NAME() AS HOST_NAME
, APP_NAME() AS APP_NAME
, SUSER_SNAME() AS AUDIT_USERID
, GETDATE() AS AUDIT_DATETIME
, ''NA'' AS [COLUMN_NAME]
, ''NA'' AS [COLUMN_OLD_VALUE]
, ''NA'' AS [COLUMN_NEW_VALUE]
FROM #deleted DEL';
END
END
IF ISNULL(@SQL_QUERY, '') ''
BEGIN
SET @SQL_QUERY = 'INSERT INTO Audit_Log.dbo.[AUDIT_LOG]
(
[TABLE_NAME]
, [PRIMARY_KEY_COLUMN_NAME]
, [PRIMARY_KEY_COLUMN_VALUE]
, [AUDIT_ACTION]
, [HOST_NAME]
, [APP_NAME]
, [AUDIT_USERID]
, [AUDIT_DATETIME]
, [COLUMN_NAME]
, [COLUMN_OLD_VALUE]
, [COLUMN_NEW_VALUE]
)' + ISNULL(@SQL_QUERY, '');
END
EXECUTE sp_executesql @SQL_QUERY
SET @ORDINAL_POSITION = @ORDINAL_POSITION + 1;
END
END TRY
BEGIN CATCH
DECLARE @ErrorNumber INT = ERROR_NUMBER();
DECLARE @ErrorLine INT = ERROR_LINE();
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
DECLARE @ErrorProcedure VARCHAR(500) = ERROR_PROCEDURE();
SET @ErrorMessage = ISNULL(@ErrorMessage, '')
+ ' Procedure Name: ' + ISNULL(@ErrorProcedure, '')
+ ' Error Number: ' + CAST(ISNULL(@ErrorNumber, 0) AS VARCHAR(10))
+ ' Line Number: ' + CAST(ISNULL(@ErrorLine, 0) AS VARCHAR(10));
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
Disclaimer : Audit Log trigger has performance overhead. It’s meant for table which is not frequently updated, deleted or inserted. Please take precaution when creating the trigger on the table having too many columns.
SQL Server Carpenter 
One thought on “Generic trigger for audit log – SQL Server”