Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74

    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:

    http://msdn.microsoft.com/library/de...ar_da_1tup.asp

    ..
    You can use the FOR clause to specify when a trigger is executed:
    AFTER
    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.

    ..

    This statement sounds confusing?

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    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.
    MCDBA

  3. #3
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74
    Thanks a lot for the info.

    achorozy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •