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?
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