SQL Server trigger execution

Question

Say I have an UPDATE trigger on tableA that inserts a new record into tableB.

CREATE TRIGGER insertIntoTableB
ON tableA
FOR UPDATE 
AS
   INSERT INTO tableB (...) VALUES (...)  
GO

I then run these statements sequentially. Will the second UPDATE statement (UPDATE tableB) work OK? (i.e. wait for the trigger on table A to fully execute)

UPDATE tableA
SET ...
WHERE key = 'some key'

UPDATE tableB
SET ...
WHERE key = 'newly inserted key from trigger'

Remus Rusanu · Accepted Answer · 2011-01-07 23:48:51Z

2

The behavior is subject to the nested triggers server configuration, see Using Nested Triggers:

Both DML and DDL triggers are nested when a trigger performs an action that initiates another trigger. These actions can initiate other triggers, and so on. DML and DDL triggers can be nested up to 32 levels. You can control whether AFTER triggers can be nested through the nested triggers server configuration option. INSTEAD OF triggers (only DML triggers can be INSTEAD OF triggers) can be nested regardless of this setting.

When a trigger on table A fires and inside the trigger table B is updated, the trigger on table B runs immediately. The Table A trigger did not finish, it is blocked in waiting for the UPDATE statement to finish, which in turn waits for the Table B trigger to finish. However, the updates to table A have already occurred (assuming a normal AFTER trigger) and querying the Table A from the table B's trigger will see the updates.

edited Jan 7, 2011 at 23:48

answered Jan 7, 2011 at 23:36

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Luke Hutton Over a year ago

sorry I may have been unclear at first, but there is only a single trigger and that is on tableA, I've updated my post accordingly

Remus Rusanu Over a year ago

I see. The first update statement will not complete before it's trigger has returned. At the moment the second UPDATE start executing, you are absolutely 100% guaranteed that the previous statement has finished, including any triggers it may have caused.

diagonalbatman · Accepted Answer · 2011-01-07 23:27:40Z

1

If the updates are sequentially coded into the UPDATE trigger of A then yes.

answered Jan 7, 2011 at 23:27

diagonalbatman

18.1k3 gold badges33 silver badges31 bronze badges

2 Comments

Luke Hutton Over a year ago

Well in the UPDATE trigger of tableA, there's is a single INSERT INTO tableB statement and the UPDATE tableA, and UPDATE tableB statements above are executed sequentially

diagonalbatman Over a year ago

I think you will be ok then. Best thing would be to test it.

Collectives™ on Stack Overflow

SQL Server trigger execution

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related