Problem UPDATE trigger when updating multiple rows

Question

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 14-May-21 6:42am

chamindat

Updated 14-May-21 6:49am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

SeanChupas · Answer 1 · 2021-05-14T06:49:00

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.