What could be disabling triggers?

Question

What could be disabling triggers?

Giorgio Vidali

Ten Centuries

Points: 1116
More actions
July 23, 2014 at 7:16 am

#291357

Everyone:
In a couple of occasions, on two different databases, on two different servers (two different customers of ours)... we found that all triggers, on all tables, had been disabled.
Unfortunately, the database servers are at the customers' locations... and, as you can imagine, we have no way to constantly monitor the state of the databases.
Also, the servers are not Enterprise Edition... so we cannot use the Audit feature to find out what actually happened...
Just to cover all basis, here is the question: is there any process or operation that can disable ALL triggers on the database.... other than explicitly doing so by executing T-SQL commands?
Thanks!
Giorgio

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Not to my knowledge. You may not be able to use Audit but you could setup database triggers to capture the use of DDL to disable triggers and even prevent it. Of course, if your DML triggers are getting disabled by someone, they could disable the database triggers as well.

Jack Corbett SSC Guru Points: 184393 More actions · Answer 2

If you catch this soon enough you can query the default trace to find out who is disabling the triggers.

It shows as an Object:Altered Event and the ObjectName column will be the table name and the Object Type will be 8277 which is a user table. It doesn't actually tell you that a trigger was disabled, but you'll know a change was made to a table, when, and by whom. You can also correlate the time based on the modify_date in sys.triggers.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Chris Wooding SSCarpal Tunnel Points: 4530 More actions · Answer 3

My guess is that they're using some tool to import data. Many of these tools offer the option to disable triggers during import and it's often the default setting.

Chris

Giorgio Vidali Ten Centuries Points: 1116 More actions · Answer 4

Thanks all for the answers and suggestions...

We believe that the problem is indeed related to someone importing data with some unspecified tool... but, of course, not one single user at these customers has admitted to this...

We may experiment with some other triggers to track the disabling of a number of specific triggers... and see what we come up with...

Thanks all again...