Unanswered: Which is completed 1st? a transaction or a trigger?
Which is completed 1st? a transaction or a trigger?
I am using SQL Server 7.0.
I have databases DB1 (only current values) and DB2 (both current and old - keeps history). When I update (or insert), on DB1, a copy of the row I am working on has to be sent to DB2 using a trigger. What gets completed first? The update process on DB1 or the action started by the trigger? I am asking this because of what I found at this site:
You can use the FOR clause to specify when a trigger is executed:
The trigger executes after the statement that triggered it completes. If the statement fails with an error, such as a constraint violation or syntax error, the trigger is not executed. AFTER triggers cannot be specified for views, they can only be specified for tables. You can specify multiple AFTER triggers for each triggering action (INSERT, UPDATE, or DELETE). If you have multiple AFTER triggers for a table, you can use sp_settriggerorder to define which AFTER trigger fires first and which fires last. All other AFTER triggers besides the first and last fire in an undefined order which you cannot control.
AFTER is the default in SQL Server 2000. You could not specify AFTER or INSTEAD OF in SQL Server version 7.0 or earlier, all triggers in those versions operated as AFTER triggers.
The update/insert (DB1) gets executed first prior to the AFTER trigger (DB2) firing. However the two actions act as one, if the trigger fails and rollbacks its transactions then the event that fired (insert/update DB1) the trigger will be rolled back as well.
As well if the update/insert fails then the trigger will not be fired.