Trigger code for Audit Trail (1 Viewer)

ozinm

Human Coffee Siphon
Local time
Today, 23:21
Joined
Jul 10, 2003
Messages
121
Help, I a bit stuck.
I'm trying to create a trigger that will launch on changes to the data in a table called tblContacts.

It should record the changes in a table called tblAudit

I've tried to make the code so it checks all fields (columns) in the tblContacts table.


The problem I'm having is I can't work out how to temporarily store the data in a variable (see "-- Get the old and new values" in the code below).

Any ideas?

Code:
ALTER TRIGGER trgContactsUpdate

ON tblContacts

FOR UPDATE

AS


DECLARE @tmp_Col_Name VARCHAR(255)
DECLARE @tmp_OldVal sql_variant
DECLARE @tmp_NewVal sql_variant
DECLARE @tmp_RecordID int

DECLARE column_cursor CURSOR FOR
SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'tblContacts'
ORDER BY ORDINAL_POSITION

OPEN column_cursor


FETCH NEXT FROM column_cursor into @tmp_Col_name

-- Check each column for changes
WHILE @@FETCH_STATUS = 0
BEGIN

-- Get the old and new values   
	select @tmp_OldVal = exec('select top 1 ' + @tmp_Col_name + ' from Deleted ')
	set @tmp_NewVal = exec('select top 1 ' + @tmp_Col_name + ' from Inserted ')

-- Get the record ID	
	select @tmp_RecordID = ( select irpContact from Inserted )

-- If there have been changes in the data...
	IF @tmp_OldVal != @tmp_NewVal

	BEGIN

-- Record the change in the Audit Table
		INSERT INTO tblAudit
		(dwoEditDate,swoUser,swoRecordID,swoSourceTable,swoSourceField,swoBeforeValue,swoAfterValue)
		VALUES
		(GETUTCDATE(),'user',@tmp_RecordID ,'tblCOntacts',@tmp_Col_name,@tmp_OldVal,@tmp_NewVal)
	END

	FETCH NEXT FROM column_cursor  into @tmp_Col_name
END

CLOSE column_cursor
DEALLOCATE column_cursor
 

pdx_man

Just trying to help
Local time
Today, 16:21
Joined
Jan 23, 2001
Messages
1,347
Here is the code I use for Table Audits. It works pretty flawlessly:
Code:
CREATE TRIGGER trg_[i]tblName[/i]_Audit    -- Change this name accordingly reflective of your trigger
------------------------------------------------------------------------------------------------------------
--&
--&	Name: trg_Pool_ID_Audit
--&
--&	Purpose: This trigger audit trails all changes made to a table.
--&		It will place in Tbl_Audit_Trans_Log all inserted, deleted, changed columns in the table on 
--&		which it is placed.  It will put out an error message if there is no primary key on the 
--&		table, so if your table does not contain a primary key, either put one on, create an 
--&		identity field and join it with a meaningful field to create a PK, or don't put this audit 
--&		trigger on the table.
--&
--&		This will track:
--&			* Type of change,(I)nsert, (U)pdate or (D)elete
--&			* Table affected
--&			* Primary Key values for the row affected
--&			* Field name being affected
--&			* Previous value
--&			* New Value
--&			* Date/Time change was done
--&			* User name of the account making the change
--&			* Name of the program executing the change
--&			* MacAddress of the machine the change was made from
--&
--&	Author: Davis C Henely (... and samples from the web)
--&
--&	Created: 02/08/2006
--&
--&	Usage: This is a general trigger that can be used on any table.  The tracking table, Tbl_Audit_Trans_Log
--&		will need to be recreated in any database where tables need to be tracked.  You cannot have
--&		changes in a table on DB XYZ tracked in the Audit table in DB ABC.  You must create a 
--&		Tbl_Audit_Trans_Log table in DB XYZ as well.
--&
--&	*****!!!!!   You will need to change @TableName to match the table to be audit trailed
--&
--&	Updates:
--&
------------------------------------------------------------------------------------------------------------

ON Tbl_Pool_ID 				-- Make sure this table name matches ...
FOR UPDATE, DELETE
AS

SET NOCOUNT ON

DECLARE @bit INT ,
	@field INT ,
	@maxfield INT ,
	@char INT ,
	@fieldname VARCHAR(128) ,
	@TableName VARCHAR(128) ,
	@PKCols VARCHAR(1000) ,
	@sql VARCHAR(2000), 
	@UpdateDate VARCHAR(50) ,
	@UserName VARCHAR(128) ,
	@Type CHAR(1) ,
	@PKSELECT VARCHAR(1000),
	@macaddr VARCHAR(50),
	@ProgName VARCHAR(100)

	SELECT @TableName = '[i]tblName[/i]'  	-- ... this table name from above and is the name of your table

	-- date AND user
	SELECT 	@UserName = system_user ,
		@UpdateDate = CONVERT(VARCHAR(50), GETDATE(), 109)

	-- Get the user's network address from where the changes came from and the name of the app they were using
	SELECT 	@macaddr = LEFT(LTRIM(RTRIM(CONVERT(VARCHAR(100), sys.net_address))),25) ,
		@ProgName = LEFT(LTRIM(RTRIM(CONVERT(VARCHAR(100), sys.program_name))),25) 
	FROM	master.dbo.sysprocesses sys (nolock)
	WHERE	sys.spid = @@spid
--		AND hostname NOT IN ('corp-db-01', ' ')

	-- Action
	IF EXISTS (SELECT * FROM inserted)
		IF EXISTS (SELECT * FROM deleted)
			SELECT @Type = 'U'
		ELSE
			SELECT @Type = 'I'
	ELSE
		SELECT @Type = 'D'
	
	-- get list of columns
	SELECT * into #ins FROM inserted
	SELECT * into #del FROM deleted
	
	-- Get primary key columns for FULL OUTER JOIN
	SELECT	@PKCols = COALESCE(@PKCols + ' AND', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
	FROM	INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
		INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
	WHERE 	pk.TABLE_NAME = @TableName
	AND	CONSTRAINT_TYPE = 'PRIMARY KEY'
	AND	c.TABLE_NAME = pk.TABLE_NAME
	AND	c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
	
	-- Get primary key SELECT for insert
	SELECT @PKSELECT = COALESCE(@PKSELECT+'+','') + '''[' + COLUMN_NAME + '=''+convert(VARCHAR(100),COALESCE(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+'']''' 
	FROM	INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
		INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
	WHERE 	pk.TABLE_NAME = @TableName
	AND	CONSTRAINT_TYPE = 'PRIMARY KEY'
	AND	c.TABLE_NAME = pk.TABLE_NAME
	AND	c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
	
	IF @PKCols IS NULL
	BEGIN
		RAISERROR('No PK on table %s', 16, -1, @TableName)
		RETURN
	END
	
	SELECT @field = 0, @maxfield = max(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
	while @field < @maxfield
	BEGIN
		SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @field
		SELECT @bit = (@field - 1 )% 8 + 1
		SELECT @bit = POWER(2,@bit - 1)
		SELECT @char = ((@field - 1) / 8) + 1
		IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 OR @Type in ('I','D')
		BEGIN
			SELECT @fieldname = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION = @field
			SELECT @fieldname = '[' + @fieldname + ']'
			SELECT @sql = 	'INSERT Tbl_Audit_Trans_Log (TransType, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName, ProgramName, MacAddress)'
			SELECT @sql = @sql + 	' SELECT ''' + @Type + ''''
			SELECT @sql = @sql + 	',''' + @TableName + ''''
			SELECT @sql = @sql + 	',' + @PKSELECT
			SELECT @sql = @sql + 	',''' + @fieldname + ''''
			SELECT @sql = @sql + 	',convert(VARCHAR(1000),d.' + @fieldname + ')'
			SELECT @sql = @sql + 	',convert(VARCHAR(1000),i.' + @fieldname + ')'
			SELECT @sql = @sql + 	',''' + @UpdateDate + ''''
			SELECT @sql = @sql + 	',''' + @UserName + ''''
			SELECT @sql = @sql + 	',''' + @ProgName + ''''
			SELECT @sql = @sql + 	',''' + @macaddr + ''''
			SELECT @sql = @sql + 	' FROM #ins i FULL OUTER JOIN #del d'
			SELECT @sql = @sql + 	@PKCols
			SELECT @sql = @sql + 	' WHERE i.' + @fieldname + ' <> d.' + @fieldname 
			SELECT @sql = @sql + 	' OR (i.' + @fieldname + ' IS NULL AND  d.' + @fieldname + ' IS NOT NULL)' 
			SELECT @sql = @sql + 	' OR (i.' + @fieldname + ' IS NOT NULL AND  d.' + @fieldname + ' IS NULL)' 
			EXEC (@sql)
		END
	END

SET NOCOUNT OFF

Here is the structure of the Audit table:
Code:
CREATE TABLE [tbl_Audit_Trans_Log] (
	[TransType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TableName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PK] [varchar] (600) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[FieldName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[OldValue] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NewValue] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[UpdateDate] [datetime] NOT NULL ,
	[UserName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ProgramName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MacAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	CONSTRAINT [PK_Tbl_Audit_Trans_Log] PRIMARY KEY  CLUSTERED 
	(
		[TableName],
		[PK],
		[FieldName],
		[UpdateDate]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO
 

Users who are viewing this thread

Top Bottom