View Single Post
Old 12-21-2007, 02:08 AM   #1
ozinm
Human Coffee Siphon
 
Join Date: Jul 2003
Location: UK
Posts: 121
Thanks: 1
Thanked 9 Times in 7 Posts
ozinm is on a distinguished road
Trigger code for Audit Trail

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

ozinm is offline   Reply With Quote