Determining if TRIGGER is active 2

[StevenK]

I am using SQL server 7.0 and have some TRIGGERS set up against tables for INSERT, UPDATE and DELETE actions. I am able to turn these on and off. What syntax should I be using to determine if a TRIGGER is currently active or not ?
Thanks in advance
Steve

 

[Andel]

I'm just wondering how would you turn on and off a trigger?

Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[tlbroadbent]

From SQL BOL:

ALTER TABLE trig_example DISABLE TRIGGER trig1
ALTER TABLE trig_example ENABLE TRIGGER trig1 Terry

http://members.home.net/tlbroadbent/index.htm

 

[Andel]

I see.... Thanks Terry.

Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[tlbroadbent]

It appears that the status on sysobjects table is updated when a trigger is abled or disabled. The disabled value is 2048 more than the enabled value.

Perhaps you could do a query like this.

select status, name from sysobjects
where type ='tr'
and name='triggername'

If status > 2048 then the trigger is disabled otherwise it is enabled. I think this will work with all triggers but cannot find any documentation to prove it. Terry

http://members.home.net/tlbroadbent/index.htm

 

[StevenK]

tlbroadbent - Thanks - I'll give that a whirl when I get to the office tomorrow.
Steve