Issue with automatic disabling of trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Issue with automatic disabling of trigger

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-07-01 : 03:42:37

Hi,

2 of my production DML triggers are automatically getting disabled how to trace what making it to disable automatically.

Thanks,
Gangadhar

Thanks,
Gangadhara MS
SQL Developer and DBA

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-07-12 : 20:05:09

(I forget if this is available in SQL 2005 or not)

Can you write a DDL Trigger that would either prevent this or at least alert you of the event?

=======================================
I have never met a man so ignorant that I couldn't learn something from him. -Galileo Galilei, physicist and astronomer (1564-1642)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-12 : 20:48:45

Trace for Object:Altered event class. You can filter for "DISABLE TRIGGER" in the TextData column as well.

maagt
Starting Member

20 Posts

Posted - 2011-09-27 : 23:21:47

Hi,
I have faced the same prob too, the trigger turned off automatically, i tried to search the logs of what making it disabled but found nothing. Is there any way i can trace why the trigger disabled automatically?

thanks.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-28 : 03:43:34

Someone is disabling it, or some job, or something from the app. SQL won't randomly disable triggers.

As Russell said, "Trace for Object:Altered event class. You can filter for "DISABLE TRIGGER" in the TextData column as well."

--
Gail Shaw
SQL Server MVP

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-09-28 : 04:35:42

yes i used profiler to trace the events and found the query which was disabling inside the stored procs.

Thanks lot for your suggestions.

Thanks,
Gangadhara MS
SQL Developer and DBA

maagt
Starting Member

20 Posts

Posted - 2011-09-28 : 05:01:16

The profiler did not turn on, that's why i can't trace the event; and i just found the problem occured due to they have restored the database, therefore all the triggers were disabled.
So now, can anyone help on how to create a script to enable the triggers in the server agent?

Thanks a lot.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2011-09-28 : 05:46:12

ALTER TABLE [name] ENABLE TRIGGER ALL

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources