We have a trigger on a table which for Insert, update or delete operations logs the inserted, deleted or updated rows to an audit table if the column DEPOSIT_ACCOUNT_NO update either before or after the operation. The trigger definition is below..
ALTER TRIGGER [dbo].[TR_IMG_PDC_ACTIVITYAUD] ON [dbo].[IMG_PDC_OUTWARDCLEARING]
FOR UPDATE AS
BEGIN
SET NOCOUNT ON;
DECLARE @EVENT_TABLE_NAME VARCHAR(100)
DECLARE @EVENT_COLUMN_NAME VARCHAR(100)
DECLARE @EVENT_PROCESS_DATE VARCHAR(10)
DECLARE @EVENT_BATCH_NO VARCHAR(14)
DECLARE @EVENT_SLIP_NO VARCHAR(15)
DECLARE @EVENT_UI VARCHAR(25)
DECLARE @EVENT_ACTIVITY VARCHAR(1000)
DECLARE @EVENT_DATETIME DATETIME
DECLARE @EVENT_USER VARCHAR(100)
DECLARE @EVENT_MACHINE VARCHAR(100)
DECLARE @EVENT_MACHINE_IP VARCHAR(100)
DECLARE @EVENT_USER_CC VARCHAR(100)
DECLARE @NEW_VALUE VARCHAR(50)
DECLARE @OLD_VALUE VARCHAR(50)
DECLARE @CHEQUE_NO VARCHAR(6)
DECLARE @USER_ID VARCHAR(50)
DECLARE @USER_NAME VARCHAR(50)
DECLARE @EVENT_USER_ID VARCHAR(50)
DECLARE @EVENT_USER_NAME VARCHAR(50)
--01------------------------------------------------------------------------------------
IF UPDATE (DEPOSIT_ACCOUNT_NO)
BEGIN
SELECT @EVENT_TABLE_NAME = 'IMG_PDC_OUTWARDCLEARING'
SELECT @EVENT_COLUMN_NAME = 'DEPOSIT_ACCOUNT_NO'
SELECT @EVENT_PROCESS_DATE = (SELECT PROCESS_DATE FROM INSERTED)
SELECT @EVENT_BATCH_NO = (SELECT BATCH_NO FROM INSERTED)
SELECT @EVENT_UI = (SELECT UNIQUE_IDENTIFIER FROM INSERTED)
SELECT @EVENT_DATETIME = (SELECT MODIFIED_DATETIME FROM INSERTED)
SELECT @EVENT_USER = (SELECT MODIFIED_USER FROM INSERTED)
SELECT @EVENT_USER_CC = (SELECT MODIFIED_USER_CC FROM INSERTED)
SELECT @EVENT_MACHINE = (SELECT MODIFIED_MACHINE FROM INSERTED)
SELECT @EVENT_MACHINE_IP = (SELECT MODIFIED_MACHINE_IP FROM INSERTED)
SELECT @EVENT_USER_ID = (SELECT MODIFIED_USER FROM INSERTED)
SELECT @EVENT_USER_NAME = (SELECT USER_NAME FROM MUSER WHERE USER_ID=@EVENT_USER_ID)
SELECT @OLD_VALUE = (SELECT DEPOSIT_ACCOUNT_NO FROM DELETED)
SELECT @NEW_VALUE = (SELECT DEPOSIT_ACCOUNT_NO FROM INSERTED)
SELECT @EVENT_ACTIVITY = ''
IF (@OLD_VALUE<>@NEW_VALUE)
SELECT @EVENT_ACTIVITY = @EVENT_USER_NAME + ' has changed the deposit account number from "' + @OLD_VALUE + '" to "' + @NEW_VALUE + '"'
IF (@OLD_VALUE IS NULL)
SELECT @EVENT_ACTIVITY = @EVENT_USER_NAME + ' has entered "' + @NEW_VALUE + '" deposit account number'
IF (@EVENT_ACTIVITY<>'')
BEGIN
INSERT INTO IMG_PDC_ACTIVITYEVENTS (EVENT_TABLE_NAME,EVENT_COLUMN_NAME,EVENT_PROCESS_DATE,EVENT_BATCH_NO,EVENT_UI,EVENT_ACTIVITY,EVENT_DATETIME,EVENT_USER,EVENT_USER_CC,EVENT_MACHINE,EVENT_MACHINE_IP)
SELECT @EVENT_TABLE_NAME,@EVENT_COLUMN_NAME,@EVENT_PROCESS_DATE,@EVENT_BATCH_NO,@EVENT_UI,@EVENT_ACTIVITY,@EVENT_DATETIME,@EVENT_USER,@EVENT_USER_CC,@EVENT_MACHINE,@EVENT_MACHINE_IP FROM INSERTED
END
END;
END;
This works fine except when multiple updates occur within a single transaction when it will error. Have tried recreating this with a cursor but I cannot get this working. Can anyone offer any tips / suggestions on how to solve this? I understand why the trigger fails but am struggling to find a solution.
Thanks in advance for any help.
END;
What I have tried:
ALTER TRIGGER [dbo].[TR_IMG_PDC_ACTIVITYAUD] ON [dbo].[IMG_PDC_OUTWARDCLEARING]
FOR UPDATE AS
BEGIN
SET NOCOUNT ON;
DECLARE @EVENT_TABLE_NAME VARCHAR(100)
DECLARE @EVENT_COLUMN_NAME VARCHAR(100)
DECLARE @EVENT_PROCESS_DATE VARCHAR(10)
DECLARE @EVENT_BATCH_NO VARCHAR(14)
DECLARE @EVENT_SLIP_NO VARCHAR(15)
DECLARE @EVENT_UI VARCHAR(25)
DECLARE @EVENT_ACTIVITY VARCHAR(1000)
DECLARE @EVENT_DATETIME DATETIME
DECLARE @EVENT_USER VARCHAR(100)
DECLARE @EVENT_MACHINE VARCHAR(100)
DECLARE @EVENT_MACHINE_IP VARCHAR(100)
DECLARE @EVENT_USER_CC VARCHAR(100)
DECLARE @NEW_VALUE VARCHAR(50)
DECLARE @OLD_VALUE VARCHAR(50)
DECLARE @CHEQUE_NO VARCHAR(6)
DECLARE @USER_ID VARCHAR(50)
DECLARE @USER_NAME VARCHAR(50)
DECLARE @EVENT_USER_ID VARCHAR(50)
DECLARE @EVENT_USER_NAME VARCHAR(50)
--01------------------------------------------------------------------------------------
IF UPDATE (DEPOSIT_ACCOUNT_NO)
BEGIN
SELECT @EVENT_TABLE_NAME = 'IMG_PDC_OUTWARDCLEARING'
SELECT @EVENT_COLUMN_NAME = 'DEPOSIT_ACCOUNT_NO'
SELECT @EVENT_PROCESS_DATE = (SELECT PROCESS_DATE FROM INSERTED)
SELECT @EVENT_BATCH_NO = (SELECT BATCH_NO FROM INSERTED)
SELECT @EVENT_UI = (SELECT UNIQUE_IDENTIFIER FROM INSERTED)
SELECT @EVENT_DATETIME = (SELECT MODIFIED_DATETIME FROM INSERTED)
SELECT @EVENT_USER = (SELECT MODIFIED_USER FROM INSERTED)
SELECT @EVENT_USER_CC = (SELECT MODIFIED_USER_CC FROM INSERTED)
SELECT @EVENT_MACHINE = (SELECT MODIFIED_MACHINE FROM INSERTED)
SELECT @EVENT_MACHINE_IP = (SELECT MODIFIED_MACHINE_IP FROM INSERTED)
SELECT @EVENT_USER_ID = (SELECT MODIFIED_USER FROM INSERTED)
SELECT @EVENT_USER_NAME = (SELECT USER_NAME FROM MUSER WHERE USER_ID=@EVENT_USER_ID)
SELECT @OLD_VALUE = (SELECT DEPOSIT_ACCOUNT_NO FROM DELETED)
SELECT @NEW_VALUE = (SELECT DEPOSIT_ACCOUNT_NO FROM INSERTED)
SELECT @EVENT_ACTIVITY = ''
IF (@OLD_VALUE<>@NEW_VALUE)
SELECT @EVENT_ACTIVITY = @EVENT_USER_NAME + ' has changed the deposit account number from "' + @OLD_VALUE + '" to "' + @NEW_VALUE + '"'
IF (@OLD_VALUE IS NULL)
SELECT @EVENT_ACTIVITY = @EVENT_USER_NAME + ' has entered "' + @NEW_VALUE + '" deposit account number'
IF (@EVENT_ACTIVITY<>'')
BEGIN
INSERT INTO IMG_PDC_ACTIVITYEVENTS (EVENT_TABLE_NAME,EVENT_COLUMN_NAME,EVENT_PROCESS_DATE,EVENT_BATCH_NO,EVENT_UI,EVENT_ACTIVITY,EVENT_DATETIME,EVENT_USER,EVENT_USER_CC,EVENT_MACHINE,EVENT_MACHINE_IP)
SELECT @EVENT_TABLE_NAME,@EVENT_COLUMN_NAME,@EVENT_PROCESS_DATE,@EVENT_BATCH_NO,@EVENT_UI,@EVENT_ACTIVITY,@EVENT_DATETIME,@EVENT_USER,@EVENT_USER_CC,@EVENT_MACHINE,@EVENT_MACHINE_IP FROM INSERTED
END
END;
END;