Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    223

    Unanswered: How to disable a trigger in duration?

    Hello, everyone:

    There is a trigger to monitor the modification on a table, and it turn on. For a special duration, I need to turn off this trigger to modify the table. And then turn on the trigger again.

    Any help will be appreciated.

    Thanks

    ZYT

  2. #2
    Join Date
    Dec 2004
    Location
    York, PA
    Posts
    95
    Comment out the actions in the trigger should do it!
    Sorry to be terse
    some say it's a curse
    I know it's worse
    I'm just diverse

  3. #3
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    There are some other cool tricks that will allow the trigger to continue to fire in production while you do maintenance.

    For example, you could have it not do anything if you are running something from your machine:
    Code:
    Create my_trigger On my_table For Insert 
    As
    If Host_Name() != N'your machine name'
    Begin
        Trigger code goes here
    End
    Don't forget to remove this when you're done or you may drive yourself batty (I know, I'm batty).

    Or, you could add a _maintenance bit column to your table (default = 0) and only execute the core trigger logic when it is not set:
    Code:
    Create my_trigger On my_table For Insert 
    As
    If (Select Count(*) From Inserted Where _maintenance = 1) = 0
    Begin
        Trigger code goes here
    End
    Else
        Update mytable Set _Maintenance = 0
        From mytable Join inserted On mytable.PK = inserted.PK
    End
    That has a higher battiness prevention rate.
    Last edited by MaxA; 02-21-05 at 18:11.
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

Posting Permissions

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