March 16, 2005 at 11:22 am
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.....
March 16, 2005 at 11:34 am
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
March 16, 2005 at 11:39 am
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 @$$.
March 16, 2005 at 12:41 pm
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....
March 17, 2005 at 10:09 pm
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?
March 17, 2005 at 10:18 pm
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....
March 18, 2005 at 6:18 am
Hehe, you guys are sneaky .
March 18, 2005 at 1:44 pm
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 😉
March 18, 2005 at 1:50 pm
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
March 19, 2005 at 1:34 pm
Brian's recommendation is safer and more informative. I sometimes have a one-track mind, and get carried away...
March 21, 2005 at 7:13 am
Thanks for the help. I will try them all and see which one gives me the best results.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply