Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    5

    Unanswered: Prevent Trigger from firing

    Hi,

    I have an update tigger on one of my tables.
    I want to fire an Update SQL but somehow prevent trigger from firing.

    Any Ideas..

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    ALTER TABLE TABLENAME DISABLE TRIGGER TRIGGERNAME

  3. #3
    Join Date
    Jan 2004
    Posts
    5
    Well that works...

    I observe that if i execute a Update statement on a table on which an update trigger has been defined.

    It does not fires the trigger again. (I mean, trigger is not fired due to update on same table if tigger itself updates the table)

    It should have fired the trigger again...

    If i make two tables and write triggers to update other table instead of itself...it fires both triggers 32 times nested and then gives an error.

    "Max nesting reached".

    Why is it so...

  4. #4
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    From BOL:
    Triggers are nested when a trigger performs an action that initiates another trigger, which can initiate another trigger, and so on. Triggers can be nested up to 32 levels, and you can control whether triggers can be nested through the nested triggers server configuration option.

    If nested triggers are allowed and a trigger in the chain starts an infinite loop, the nesting level is exceeded and the trigger terminates
    --------------
    Also check recursive triggers in BOL.
    regards,
    harshal.

  5. #5
    Join Date
    Jan 2004
    Posts
    5
    Ok try this....

    Create a table, write a update trigger in itself.

    Eg :

    update mytable set Count = Count + 1

    Now execute

    update mytable set Count = 0

    It should go into infinite loop as update tigger updates the same table...and the same trigger shoul dbe fired again...

    But observe the difference ...(i am using SQL server 2000)

    Thanks...
    Naveen

  6. #6
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    yes I get u'r poit this is what bol says

    If nested triggers are allowed and a trigger in the chain starts an infinite loop, the nesting level is exceeded then the trigger terminates.
    to avoid this infinite loop this care is been taken.

Posting Permissions

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