How to Monitor or Identify Dropping of Triggers in DB

Question

How to Monitor or Identify Dropping of Triggers in DB

ofern

Old Hand

Points: 399
More actions
March 16, 2005 at 11:22 am

#88292

Is there a way to monitor or identify when a trigger is dropped. We have several developers that are dropping trigger to update table info and we need to track this.
Thanks.....

Viewing 11 posts - 1 through 10 (of 10 total)

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

You can run a profiler or SQL Server trace and track the Objecteleted event. This will tell you after the fact that such an event has occurred. Make sure you include the ObjectName or ObjectID data column.

K. Brian Kelley
@kbriankelley

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Well the first thing I would do is to tell the programmers to stop dropping the triggers for no reason and show them how to use those commands :

--disable and enable a trigger

ALTER TABLE dbo.Devise DISABLE TRIGGER trDevise_UpdaterPrixVente

ALTER TABLE dbo.Devise ENABLE TRIGGER trDevise_UpdaterPrixVente

--disable and enable all triggers

ALTER TABLE dbo.Devise DISABLE TRIGGER ALL

ALTER TABLE dbo.Devise ENABLE TRIGGER ALL

You could also do snapshots of the sysobjects table for any missing triggers. (create a permanent table and insert all the relevant trigger info to allow you to find missing triggers using a left join). You would simply have to add this into a job that runs every ?? minutes and Voila.

The downside is that you would have to keep your current list of triggers up to date during developpement... which could be a pretty big pain in the @$$.

ofern Old Hand Points: 399 More actions · Answer 3

Sorry, Actually they are not dropping the triggers, they are just disable and enabling them.

This triggers are in about 120 tables, so I need a global way to monitor this behavior, not by object. Also, I need to be constant, allways monitoring.

Thanks....

Fred Williams-134192 SSCrazy Points: 2482 More actions · Answer 4

Not sure how to monitor/audit this problem with acceptable results (polling for locks on and/or changes to sysobjects seems pretty iffy; too bad you cannot put triggers on system tables).

Have you considered revoking permissions on ALTER TABLE, or even much more drastic permission restrictions?

Fred Williams-134192 SSCrazy Points: 2482 More actions · Answer 5

Just had an idea (try this somewhere besides production first!)

begin transaction

select * from sysobjects with (holdlock, xlock) where type = 'TR'

Then wait and see who gets blocked....

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Ninja's_RGR'us

SSC Guru

Points: 294069

March 18, 2005 at 6:18 am

#547759

Hehe, you guys are sneaky .

Fred Williams-134192 SSCrazy Points: 2482 More actions · Answer 7

Well, my idea didn't work, but a close relative will:

sp_configure 'allow_updates', 1

reconfigure with override

begin transaction

update sysobjects set xtype = 'TR' where xtype = 'TR'

This WILL block any attempts to disable the triggers, while allowing ordinary trigger execution. Again, try this on a non-critical environment first - allow_updates might be dangerous with "cowboy" developers, so definately don't leave it set for a long time. Then poll master..sysprocesses in a loop with a WAITFOR, looking for blocked processes with a long wait - you get the idea, I'm sure...

With some developers, you have to be sneaky 😉

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 8

Why not just run a trace and then import the trace to a table. Then use standard T-SQL to interrogate for DISABLE TRIGGER. You'll know who and when.

K. Brian Kelley
@kbriankelley

Fred Williams-134192 SSCrazy Points: 2482 More actions · Answer 9

Brian's recommendation is safer and more informative. I sometimes have a one-track mind, and get carried away...

ofern Old Hand Points: 399 More actions · Answer 10

Thanks for the help. I will try them all and see which one gives me the best results.