Generic trigger for maintaining the Audit Log

,

STEPS FOR IMPLEMENTATION:

STEP 1: Create the following tables on the same database or on separate Audit Log Database.

I have used separate 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.

/*
	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

Rate

2.33 (3)

Share

Share

Rate

2.33 (3)