Auditing for DISABLE / ENABLE IN SQL Triggers

Question

I need a way to audit when someone tries to either activate or disable a trigger in our database. The DDL launch alternative works fine, but only if the user uses

ALTER TABLE <tableName> ENABLE TRIGGER <triggerName> 

OR

 ALTER TABLE <tableName> DISABLE TRIGGER <triggerName> 

statement. From what I determined, the DDL method becomes useless if the user executes the following instructions that bypass the ALTER command:

 DISABLE TRIGGER <triggerName> ON <tableName> ENABLE TRIGGER <triggerName> ON <tableName> 

I had several thoughts about capturing these events; none of them work. One of them was, if I could access the table underlying the sys.triggers view, I could place the insert / update trigger in that table and filter the trigger name to get the audit; but my suspicion is that this is likely to lead to infinite recursion, even if it were possible.

Does anyone have any possible suggestions for alternative solutions to this problem? I do not understand why MS would allow extended versions of operators to avoid audit coverage. That is, an audit of the simplest methods; using the SQL profiler seems to be superfluous for this.

Answer 1

I would first address this through permissions. No one, except for a few dbas, should have access rights to the tables on prod and, therefore, cannot use the enable or disable trigger. If the application uses it, this should stop it. There is no excuse for anyone to modify tables. If you want to do this, you have a design flaw. If you have actual shutdown triggers, you definitely have a design flaw. Any code with the trigger disabled must be a huge red flag while viewing the code. It is not permissible to disable the trigger in the application code. This is something that should only be done by dbas who have the experience to know when to do it. If you are writing application code and, apparently, you must disable the trigger to make your code work, then your code is incorrect or the trigger must be overwritten, disabling triggers should only happen in the rarest cases.