Disable Trigger

[fluteplr]

In SQL Server 2000 is there a way to disable a specific trigger without actually deleting it?

 

[SQLSister]

Personally, I would script the trigger, then drop it do what you need to do and the run the script to put the trigger back on.

I also would probably only do this when in single user mode or when I knew no users would be entering or deleting data because if they entered something while the trigger didn't exist, the triggering action wouldn't happen which could have bad implications for your data integrity.

Another choice is to put if statements in the trigger, so that the main processes of the trigger wouldn't happen under specific conditions.

 

[fluteplr]

Yeah I know all that, I just have this memory of seeing a way to disable it rather than drop it and it is bugging me. If course it may have been on a different platform.

 

[sunila7]

Hi,

u can disable trigger on a table like this

Alter Table tbl DISABLE TRIGGER trg

*trg---- is the triger name on the table

u can use ENABLE to enable a trigger

Sunil

 

[fluteplr]

thanks I know it was in there somewhere. Senility is setting in.

 

[fluteplr]

thanks I knew it was in there somewhere. Senility is setting in.

 

[tlbroadbent]

You can also disable or enable all triggers on a table in the same statement. This is very convenient.

Alter Table tbl DISABLE TRIGGER ALL
Alter Table tbl ENABLE TRIGGER ALL Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.