CODEWITHSUNDEEP
sqlsql-servertriggers
Mumtaz Ali
Mumtaz Ali

Reputation: 23

Update Trigger For Multiple Rows

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.

Upvotes: 1

Views: 6226

Answers (2)

3BK
3BK

Reputation: 1348

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

Upvotes: 1

Arockia Nirmal
Arockia Nirmal

Reputation: 777

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

Upvotes: 0

Related Questions