Update Trigger For Multiple Rows

Question

I am trying to Insert data in a table named "Candidate_Post_Info_Table_ChangeLogs" whenever a record is updated in another table named "Candidate_Personal_Info_Table". my code works fine whenever a single record is updated but when i try to updated multiple rows it gives error:

"Sub query returned more then 1 value".

Following is my code :

ALTER TRIGGER [dbo].[Candidate_PostInfo_UPDATE]
   ON [dbo].[Candidate_Post_Info_Table]
AFTER UPDATE
AS
BEGIN
   IF @@ROWCOUNT = 0
   RETURN

       DECLARE @Candidate_Post_ID int
       DECLARE @Candidate_ID varchar(50)
       DECLARE @Action VARCHAR(50)
       DECLARE @OldValue VARCHAR(MAX)
       DECLARE @NewValue VARCHAR(MAX)
       DECLARE @Admin_id int

       IF UPDATE(Verified)
       BEGIN
            SET @Action = 'Changed Verification Status'
            SET @Candidate_Post_ID = (Select ID From inserted)
            SET @Candidate_ID = (Select Identity_Number from inserted)
              SET @NewValue = (Select Verified From inserted)
              SET @OldValue = (Select Verified From deleted)
              IF(@NewValue != @OldValue)
              BEGIN
              INSERT INTO  Candidate_Post_Info_Table_ChangeLogs(Candidate_Post_ID, Candidate_ID,  Change_DateTime, action, NewValue, OldValue, Admin_ID)
                VALUES(@Candidate_Post_ID, @Candidate_ID, GETDATE(), @Action,  @NewValue, @OldValue, '1')
                END
       END

END

i have searched stack overflow for this issue but couldn't get any related answer specific to this scenario.

*Really?* If you search on Stack overflow for questions relating to triggers and SQL Server, I'd guess that fully half of them will discuss the fact that you need to treat `inserted` and `deleted` as tables *because* they can contain more than one row. — Damien_The_Unbeliever, Feb 28 '17 at 08:27
you have at least 4 errors. If you insert more than one row INSERTED table has more than one row so you can't write: SET @Candidate_Post_ID = (Select ID From inserted) — Joe Taras, Feb 28 '17 at 08:32

score 1 · Accepted Answer · answered Feb 28 '17 at 08:49

When you insert/update multiple rows into a table, the Inserted temporary table used by the system holds all of the values from all of the rows that were inserted or updated.

Therefore, if you do an update to 6 rows, the Inserted table will also have 6 rows, and doing something like this:

SET @Candidate_Post_ID = (Select ID From inserted)

Will return an error, just the same as doing this:

SET @Candidate_Post_ID = (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6)

From the looks of things, you tried to do this with an iterative approach. Set-based is better. Maybe consider doing it like this in the body of your TRIGGER (without all of the parameters...):

IF UPDATE(Verified)
BEGIN
    INSERT INTO Candidate_Post_Info_Table_ChangeLogs 
    (
         Candidate_Post_ID
        ,Candidate_ID
        ,Change_DateTime
        ,action
        ,NewValue
        ,OldValue
        ,Admin_ID
    )
    SELECT 
         I.ID
        ,I.Identity_Number
        ,GETDATE()
        ,'Changed Verification Status'
        ,I.Verified
        ,O.Verified
        ,'1'
    FROM Inserted I
    INNER JOIN Deleted O
        ON I.ID = O.ID -- Check this condition to make sure it's a unique join per row
    WHERE I.Verified <> O.Verified
END

Thank u very much sir. i was confused about how to use a join in this scenario. it solved my problem. :) — Mumtaz Ali, Feb 28 '17 at 09:36

score 0 · Answer 2 · edited May 23 '17 at 11:46

0

A similar case was solved in the following thread using cursors.... please check it

SQL Server A trigger to work on multiple row inserts

Also the below thread gives the solution based on set based approach

SQL Server - Rewrite trigger to avoid cursor based approach

*Both the above threads are from stack overflow...

edited May 23 '17 at 11:46

Community

1
1

answered Feb 28 '17 at 08:35

Arockia Nirmal

737
1
6
20

Update Trigger For Multiple Rows

2 Answers2