Click here to Skip to main content
14,970,251 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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;
Posted
Updated 14-May-21 6:49am

1 solution

You cannot do it that way because Inserted and Deleted are tables with multiple rows, as you have found out. Your method only works on one row at a time, which is very inefficient. And running a cursor in a trigger is a terrible idea.

There are lots of examples online of how to audit sql changes. Here is one example, Create a Simple SQL Server Trigger to Build an Audit Trail[^]

Inserts and Deletes are easy. For example an insert does not need to go through all columns and neither does delete:
SQL
INSERT INTO table_audit(id, change_user, change_category, field, previous_value, changed_to_value)
SELECT i.id, i.change_user, 'I', NULL, NULL, NULL
FROM inserted i


UPDATE is the trickier one because you need to loop through or test each field (there are various techniques to do so) and log each change separately.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900