SQL Server Performance

How trigger works?

Discussion in 'SQL Server 2005 General DBA Questions' started by AJITH123, Mar 18, 2008.

  1. AJITH123 Member

    I have a doubt on trigger, the question is how trgger will fire when an update or insert happen in the table? If i do a batch update, will trigger fire for each record or it will just insert the same in to the logical table 'inserted' and do the rest of the activities ?
    I would like to know what internally happening against trigger when we do an update or delete etc?
  2. satya Moderator

    Not sure with version of SQL you are using, triggers fire only once per statement, not once per affected row.
    When a trigger that contains a rollback is executed from a SQL batch the entire batch is canceled. Thus, all data that was modified by the triggering action is rolled back by the trigger's ROLLBACK TRANSACTION statement. However, a rollback does not cause the trigger to stop executing statements. Any statements following a ROLLBACK TRANSACTION statement are executed, which is fine, but there are consequences to putting statements after a ROLLBACK TRANSACTION. Specifically, as a trigger continues to execute any remaining statements after the rollback, any modifications that happen after the rollback are not rolled back. This occurs because once a ROLLBACK TRANSACTION has been issued within the trigger, the transaction is closed. Thus, when a new action query statement is executed, a new transaction begins separate from the original. Therefore, it is generally not recommended that you put any statements after a ROLLBACK TRANSACTION
  3. Adriaan New Member

    True when the trigger is fired from an action carried out by SQL Server.
    However, I know that if you have MS Access front-end with ODBC-linked tables, then the Jet engine will sometimes update one row at a time, and the trigger will fire for each row in turn.
    Make sure that the processing in your trigger is set-based, to avoid unpleasant surprises.
  4. AJITH123 Member

    Thanks for the reply...

Share This Page