CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Pooya
Pooya

Reputation:

Update trigger behavior in SQL 2005

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?

Upvotes: 1

Views: 809

Answers (3)

Learning
Learning

Reputation: 8175

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.

Upvotes: 0

Dave Markle
Dave Markle

Reputation: 97671

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.

Upvotes: 2

Marc Gravell
Marc Gravell

Reputation: 1062570

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.

Upvotes: 2

Related Questions