Update trigger behavior in SQL 2005

Question

I used an Update statement inside a procedure to update a table which has an update trigger. Does the update statement complete after the trigger completes or what?

Answer 1

the trigger runs as part of the UPDATE statement (after the data in the table has been updated); the proc resumes after this. There are also "instead of" triggers that replace the UPDATE statement.

See here for more.

Answer 2

There are two types of triggers in SQL Servers. INSTEAD OF triggers, and AFTER triggers. By default, a trigger is an AFTER trigger, meaning this is what happens. Consider TableA, with an UPDATE AFTER TRIGGER which updates TableB.

• Issue statement: UPDATE TableA set XXX = 5;
• TableA gets updated
• The trigger fires, and TableB gets updated.

Answer 3

triggers are attached to the statement(s) that trigger them and are implicitly the part of transaction that fired them.

For ex :

if triggers are fired because of update , then it helps to understand that database would implicitly insert a begin tran and end tran surrounding that update.