Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    100

    Unanswered: Turning triggers on and off.

    Hi is it possible to run a certain SQL statement agaisnt SQL Server and ask it not to fire any triggers? Or is would it be better to disable the trigger and then reable it after ward? If so how? Thanks Ed

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    For support purposes, we created a SQLServer user named NoTriggerUser, and added code to the triggers to check the CURRENT_USER for that userid, and bypass the code if true.

  4. #4
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74

    We have another way of going around this ...

    In the business system I'm using we have a field called NoTriggerExec. There's a

    if Update(NoTriggerExec) return

    statement in the beginning of all update triggers in that business system. That way we can choose in our update's if we want the trigger to fire or not, regardless of who is doing the update in question. This is particularily useful when you need to update the row being updated, and not make the trigger go into the infamous @@Nestlevel 32 ...

    The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents.

Posting Permissions

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