CODEWITHSUNDEEP
sql-servert-sqldatabase-trigger
neo
neo

Reputation: 75

Trigger is not fired for all rows in case of update(random)

I have created a single trigger to catch all insert, update and delete operations in a replica table but in case of update, two rows are inserted one with before update values and another row with after update values but it is not working as expected.

I have tried all possible ways to do it. Now, I want to know whether I should use transaction at trigger level for update operation or should try something else.

ALTER TRIGGER [dbo].[trgAfterInsertUpdateDelete_xyz] ON [dbo].[xyz] 
FOR UPDATE,INSERT, DELETE
AS
declare @accountID int;
declare @billingDate date;
declare @amount decimal(18, 2);

---- Get data from inserted/ updated
select @accountID = i.AccountID from inserted i;    
select @billingDate=i.BillingDate from inserted i;  
select @amount=i.Amount from inserted i;

-- Insert Case
IF EXISTS( SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) 
BEGIN
    insert into xyz_Audit
    (AccountID, BillingDate, Amount, Audit_Action) 
    values(@accountID,@billingDate,@amount,'INSERT');
END

-- Update Case
IF EXISTS( SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
BEGIN
    INSERT INTO xyz_Audit
    (AccountID, BillingDate, Amount, Audit_Action)
    SELECT d.AccountID, d.BillingDate, d.Amount,             
            'BeforeUpdate'  FROM Inserted i
    INNER JOIN Deleted d ON i.ID = d.ID

    INSERT INTO xyz_Audit
    (AccountID, BillingDate, Amount,Audit_Action) 

         values(@accountID,@billingDate,@amount,'AfterUpdate');
END


-- Delete Case
IF EXISTS( SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted)
BEGIN
    INSERT INTO xyz_Audit
    (AccountID, BillingDate, Amount, Audit_Action) 
    select accountID,billingDate,amount, 'DELETE'
    from deleted
END

Image showing data saved in replica table

I expected that if 9 records are updated then there should be before and after update values for all rows but sometimes it skips. As you can see for Inst.8 there should be before and after rows but it has captured only before row and then insert but in actual there was no row deleted and inserted, only updation was done. Sometime out of 9, it pick only before rows not after update rows or sometime 2 or 3 after update rows not before update rows.

Upvotes: 0

Views: 388

Answers (1)

Squirrel
Squirrel

Reputation: 24783

your trigger assuming that only one row will be affected for insert / update / delete operatons. Which means the inserted or deleted may contains multiple rows

and the following part does not handle that.

---- Get data from inserted/ updated
select @accountID = i.AccountID from inserted i;    
select @billingDate=i.BillingDate from inserted i;  
select @amount=i.Amount from inserted i;

Actually you don't required the above at all. You should just insert into the Audit table directly from the inserted or deleted table

For example the "INSERT CASE" should be

-- Insert Case
IF EXISTS( SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) 
BEGIN
    insert into xyz_Audit (AccountID, BillingDate, Amount, Audit_Action) 
    select AccountID, BillingDate, Amount, Audit_Action= 'INSERT'
    from   inserted;
END

similarly you need to change accordingly for the Update section. And you are already doing it correctly for the delete section

Upvotes: 2

Related Questions