Generic trigger for maintaining the 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_HEADER]
(
[ID] NUMERIC NOT NULL IDENTITY(1, 1) CONSTRAINT PK_AUDIT_LOG_HEADER_ID PRIMARY KEY CLUSTERED
, [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_HEADER_AUDIT_DATETIME DEFAULT(GETDATE())
)

CREATE TABLE [AUDIT_LOG_DETAIL]
(
[ID] NUMERIC NOT NULL IDENTITY(1, 1) CONSTRAINT PK_AUDIT_LOG_DETAIL_ID PRIMARY KEY CLUSTERED
, [HEADERID] NUMERIC NOT NULL CONSTRAINT FK_AUDIT_LOG_DETAIL_HEADERID FOREIGN KEY REFERENCES AUDIT_LOG_HEADER (ID)
, [COLUMN_NAME] SYSNAME NOT NULL 
, [COLUMN_OLD_VALUE] NVARCHAR(MAX) 
, [COLUMN_NEW_VALUE] NVARCHAR(MAX) 
)

/*
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_HEADER]
(
[ID]NUMERIC NOT NULL IDENTITY(1, 1)CONSTRAINT PK_AUDIT_LOG_HEADER_ID PRIMARY KEY CLUSTERED
, [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_HEADER_AUDIT_DATETIME DEFAULT(GETDATE())
)

CREATE TABLE [AUDIT_LOG_DETAIL]
(
[ID]NUMERICNOT NULL IDENTITY(1, 1)CONSTRAINT PK_AUDIT_LOG_DETAIL_ID PRIMARY KEY CLUSTERED
, [HEADERID]NUMERICNOT NULL CONSTRAINT FK_AUDIT_LOG_DETAIL_HEADERID FOREIGN KEY REFERENCES AUDIT_LOG_HEADER (ID)
, [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", "AUDIT_LOG_HEADER" & "AUDIT_LOG_DETAIL" 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_HEADER" & "AUDIT_LOG_DETAIL" 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_DETAIL" 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_DETAIL" table in case of insert.
3) The trigger will not write any row in "AUDIT_LOG_DETAIL" table in case of delete.
4) The trigger will write every event/action such as insert, update and delete in "AUDIT_LOG_HEADER" table.
*/CREATE TRIGGER TR_Audit_Log_Hdr_Client
ON Hdr_Client
FOR INSERT, UPDATE, DELETE
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..#unique_primary_key_values') IS NOT NULL DROP TABLE #unique_primary_key_values;
CREATE TABLE #unique_primary_key_values
(
[PRIMARY_KEY_COLUMN_NAME]SYSNAME
, [ROWID]NUMERIC 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_SCHEMASYSNAME
DECLARE @TABLE_NAMESYSNAME
DECLARE @AUDIT_ACTIONCHAR(1)

DECLARE @PRIMARY_KEY_COLUMN_NAMESYSNAME
DECLARE @PRIMARY_KEY_COLUMN_VALUENVARCHAR(MAX)

DECLARE @PARAMETER_DEFINITIONNVARCHAR(MAX)
DECLARE @SQL_QUERYNVARCHAR(MAX)

DECLARE @COLUMN_NAMESYSNAME
DECLARE @ORDINAL_POSITIONINT

DECLARE @COLUMN_OLD_VALUENVARCHAR(MAX)
DECLARE @COLUMN_NEW_VALUENVARCHAR(MAX)

DECLARE @HEADERIDNUMERIC

DECLARE @ROWIDNUMERIC

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;

SET @SQL_QUERY='WITH cte_unique_primary_key_values 
AS
(
SELECT [' + @PRIMARY_KEY_COLUMN_NAME + '] AS PRIMARY_KEY_COLUMN_NAME
FROM #inserted
UNION 
SELECT [' + @PRIMARY_KEY_COLUMN_NAME + '] AS PRIMARY_KEY_COLUMN_NAME
FROM #deleted
)

INSERT INTO #unique_primary_key_values (PRIMARY_KEY_COLUMN_NAME, ROWID)
SELECT PRIMARY_KEY_COLUMN_NAME, 
ROW_NUMBER() OVER(ORDER BY (SELECT PRIMARY_KEY_COLUMN_NAME)) AS ROWID
FROM cte_unique_primary_key_values;'

EXECUTE sp_executesql @SQL_QUERY

SET @ROWID = 1
WHILE EXISTS (SELECT 1 FROM #unique_primary_key_values WHERE ROWID = @ROWID)
BEGIN
-- Get Primary Key Column Value
BEGIN
SET @PRIMARY_KEY_COLUMN_VALUE = NULL

SET @SQL_QUERY='SELECT @PRIMARY_KEY_COLUMN_VALUE = PRIMARY_KEY_COLUMN_NAME 
FROM #unique_primary_key_values 
WHERE ROWID = @ROWID';

SET @PARAMETER_DEFINITION=  '@ROWIDNUMERIC
, @PRIMARY_KEY_COLUMN_NAMESYSNAME
, @PRIMARY_KEY_COLUMN_VALUENVARCHAR(MAX) OUTPUT';

EXECUTE sp_executesql @SQL_QUERY
, @PARAMETER_DEFINITION
, @ROWID
, @PRIMARY_KEY_COLUMN_NAME
, @PRIMARY_KEY_COLUMN_VALUEOUTPUT;
END

-- Maintain Audit Log Header
INSERT INTO Audit_Log.[dbo].[AUDIT_LOG_HEADER]
(
[TABLE_NAME]
, [PRIMARY_KEY_COLUMN_NAME]
, [PRIMARY_KEY_COLUMN_VALUE]
, [AUDIT_ACTION]
, [HOST_NAME]
, [APP_NAME]
, [AUDIT_USERID]
, [AUDIT_DATETIME]
)
VALUES 
(
@TABLE_NAME
, @PRIMARY_KEY_COLUMN_NAME
, @PRIMARY_KEY_COLUMN_VALUE
, @AUDIT_ACTION
, HOST_NAME()
, APP_NAME()
, SUSER_SNAME()
, GETDATE()
)

SET @HEADERID=SCOPE_IDENTITY();

-- 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;

-- Get the old value of the column against the supplied Primary Key
IF @AUDIT_ACTION IN ('U', 'D')
BEGIN
SET @COLUMN_OLD_VALUE=NULL

SET @SQL_QUERY='SELECT @COLUMN_OLD_VALUE = [' + @COLUMN_NAME + '] 
FROM #deleted
WHERE [' + @PRIMARY_KEY_COLUMN_NAME + '] = @PRIMARY_KEY_COLUMN_VALUE';

SET @PARAMETER_DEFINITION=  '@PRIMARY_KEY_COLUMN_NAMESYSNAME
, @PRIMARY_KEY_COLUMN_VALUENVARCHAR(MAX)
, @COLUMN_NAMESYSNAME
, @COLUMN_OLD_VALUENVARCHAR(MAX) OUTPUT';

EXECUTE sp_executesql @SQL_QUERY
, @PARAMETER_DEFINITION
, @PRIMARY_KEY_COLUMN_NAME
, @PRIMARY_KEY_COLUMN_VALUE
, @COLUMN_NAME
, @COLUMN_OLD_VALUEOUTPUT;
END

-- Get the new value of the column against the supplied Primary Key
IF @AUDIT_ACTION IN ('U', 'I')
BEGIN
SET @COLUMN_NEW_VALUE=NULL

SET @SQL_QUERY='SELECT @COLUMN_NEW_VALUE = [' + @COLUMN_NAME + '] 
FROM #inserted
WHERE [' + @PRIMARY_KEY_COLUMN_NAME + '] = @PRIMARY_KEY_COLUMN_VALUE';

SET @PARAMETER_DEFINITION=  '@PRIMARY_KEY_COLUMN_NAMESYSNAME
, @PRIMARY_KEY_COLUMN_VALUENVARCHAR(MAX)
, @COLUMN_NAMESYSNAME
, @COLUMN_NEW_VALUENVARCHAR(MAX) OUTPUT';

EXECUTE sp_executesql @SQL_QUERY
, @PARAMETER_DEFINITION
, @PRIMARY_KEY_COLUMN_NAME
, @PRIMARY_KEY_COLUMN_VALUE
, @COLUMN_NAME
, @COLUMN_NEW_VALUEOUTPUT;
END

-- In case of Insert, maintain New Value
-- In case of Update, maintain both Old and New Value but only if both Old and New Value are different
-- In case of Delete, don't maintain either of the values
IF ((@AUDIT_ACTION = 'I') OR (@AUDIT_ACTION = 'U' AND ISNULL(@COLUMN_OLD_VALUE, '') <> ISNULL(@COLUMN_NEW_VALUE, '')))
BEGIN
INSERT INTO Audit_Log.[dbo].[AUDIT_LOG_DETAIL]
(
HEADERID
, COLUMN_NAME
, COLUMN_OLD_VALUE
, COLUMN_NEW_VALUE
)
VALUES 
(
@HEADERID
, @COLUMN_NAME
, @COLUMN_OLD_VALUE
, @COLUMN_NEW_VALUE
)
END

SET @ORDINAL_POSITION = @ORDINAL_POSITION + 1;
END

SET @ROWID = @ROWID + 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