/*
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] 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)
) 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_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 DECLARE @COLUMN_OLD_VALUE NVARCHAR(MAX)
DECLARE @COLUMN_NEW_VALUE NVARCHAR(MAX) DECLARE @HEADERID NUMERIC DECLARE @ROWID NUMERIC 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 = '@ROWID NUMERIC
, @PRIMARY_KEY_COLUMN_NAME SYSNAME
, @PRIMARY_KEY_COLUMN_VALUE NVARCHAR(MAX) OUTPUT'; EXECUTE sp_executesql @SQL_QUERY
, @PARAMETER_DEFINITION
, @ROWID
, @PRIMARY_KEY_COLUMN_NAME
, @PRIMARY_KEY_COLUMN_VALUE OUTPUT;
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_NAME SYSNAME
, @PRIMARY_KEY_COLUMN_VALUE NVARCHAR(MAX)
, @COLUMN_NAME SYSNAME
, @COLUMN_OLD_VALUE NVARCHAR(MAX) OUTPUT'; EXECUTE sp_executesql @SQL_QUERY
, @PARAMETER_DEFINITION
, @PRIMARY_KEY_COLUMN_NAME
, @PRIMARY_KEY_COLUMN_VALUE
, @COLUMN_NAME
, @COLUMN_OLD_VALUE OUTPUT;
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_NAME SYSNAME
, @PRIMARY_KEY_COLUMN_VALUE NVARCHAR(MAX)
, @COLUMN_NAME SYSNAME
, @COLUMN_NEW_VALUE NVARCHAR(MAX) OUTPUT'; EXECUTE sp_executesql @SQL_QUERY
, @PARAMETER_DEFINITION
, @PRIMARY_KEY_COLUMN_NAME
, @PRIMARY_KEY_COLUMN_VALUE
, @COLUMN_NAME
, @COLUMN_NEW_VALUE OUTPUT;
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 | 
