CODEWITHSUNDEEP
sqlsql-servert-sql
Sid
Sid

Reputation: 765

Triggers: Instead of / For Insert, which will trigger first?

I am trying to understand a Document of stored procedures and triggers where it inserts and updates a table with a trigger.

They gave me 2 trigger documents and there are two triggers for this particular table:

trig_table_ii and trig_table_ti.

Which will trigger first when table is inserted/updated?

In trig_table_ii ( INSTEAD OF INSERT ) there is an INSERT Statement here for that same TABLE. Does that mean trig_table_ii executes first?

Upvotes: 0

Views: 2243

Answers (2)

Abhishek Newase
Abhishek Newase

Reputation: 1

trig_table_ii : instead of insert -> The triggers that fire instead of performing the execution of DML statements (Insert, Update, and delete) are instead of triggers. In your case, DML will be insert. trig_table_ti : for insert The triggers that fire after performing the execution of DML statements (Insert, Update, and delete) are After/For triggers. In your case, DML will be insert.

Question. Which trigger will fire first or trigger first? Answer. Instead of triggers are fired even before the updates (insert operation) are made in the underlying base tables. So, Definitely, Instead of trigger will fire first. But, here is an important catch. Instead of trigger will execute statements which is inside the create trigger block even if there is some issues with the original insert statement. On the other hand, After/For Trigger will not fire because it will stop if original insert does not work properly.

Upvotes: 0

fnightangel
fnightangel

Reputation: 426

None will fire when table is updated. FOR INSERT is the same of AFTER INSERT. This will fire after you finish inserting a record.

INSTEAD OF INSERT will fire as a replacement of your insert. This trigger will ignore your original insert statement and execute whatever is there to execute.

Lets say in your INSTEAD OF tigger you, for some reason, do not insert the record. So, FOR INSERT trigger will not be fired.

Check here for more info: CREATE TRIGGER

Upvotes: 2

Related Questions