SQL Server "after update" trigger firing before update

0
votes

I have an automated workflow which works on the basis of triggers.

There is an "AFTER UPDATE" Trigger on one of my table and it has a status field, once that field is updated from "Processing" to "Completed" the trigger must fire. And in normal case as per my understanding the trigger should fire after the status is updated to "Completed".

But in my case the trigger fires and results in potential deadlock or error and the status is still "Processing".

AS per MSDN "AFTER" specifies that the trigger is fired only when all operations specified in the triggering SQL statement have executed successfully.

Is there anything i can do to fire the triggers only once the update is committed to "Completed" status?

Or does it mean that the "AFTER UPDATE" is just something which will begin as a transaction and then once trigger fires successfully the status is committed?

1
sqlsql-servertriggers
There must be something else updating that table. Are you storing the inserted and deleted values somewhere? This would point you towards what is firing the trigger early. -- Also, you can check for a change to completed status and either abort or continue the trigger based on that. – Joe C
The "after update" is still before the commit. – Glenn
So after update trigger wont work as mentioned in MSDN? – John VM

1 Answers

0
votes

Once the status is updated to "Completed" the "AFTER UPDATE" trigger fires, but the data is not yet committed. It would wait for the trigger to complete, in case of error in the trigger it would rollback the updated data.