CODEWITHSUNDEEP
sql-servertriggerstransactions
Radoslav
Radoslav

Reputation: 85

SQL Server : trigger after transaction

I have a trigger after update on table and there are 2 updates in one transaction. The trigger has been called after the first update. I expect, trigger view updates after whole transaction, but it is no true:

My transaction:

  1. update table
  2. call trigger, trigger see update only from 1
  3. update table
  4. no call trigger, because update condition in 3 don't match

It is possible to set trigger then trigger see data after transaction (from 1 as well as 3)?

My trigger:

ALTER TRIGGER "dbo"."TRIGGER" 
ON TABLE
FOR UPDATE
AS
    IF (SELECT COUNT(*) FROM inserted i, deleted d
        WHERE d.aaa IS NULL 
          AND i.aaa IS NOT NULL 
          AND i.bbb IN ('0', '1', '6', '9')) > 0
    BEGIN
        INSERT INTO export (aaa, ccc) 
            (SELECT aaa, ccc FROM inserted)
    END

Init data: insert into TABLE(aaa,bbb,ccc) values(123,'N',100)

Updates:

update TABLE set bbb = '0' where aaa = 123
update TABLE set ccc = 1 where aaa = 123

Expect: insert into export(123,1)

But it calls: insert into export(123,100)

Upvotes: 3

Views: 4629

Answers (1)

dean
dean

Reputation: 10098

I see your problem.

However, triggers in SQL Server are statement level, unfortunatelly they don't work like what you expect here. You could probably make it work by adding some "control" column to the table (updating it at the very end of transaction and create some logic around it) or using context_info, but it really smells IMHO.

Move away from the trigger and do the insert into export table from the main scope, it's your best option.

Upvotes: 3

Related Questions