Generic trigger for audit log – SQL Server

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s