http://www.sqlservercentral.com/blogs/steve_jones/2012/08/21/disabling-ddl-triggers/

Printed 2014/10/22 06:56AM

Disabling DDL Triggers

By Steve Jones, 2012/08/21

Suppose you want to stop using a DDL trigger for a short period of time, such as the login trigger I created recently. If you want to disable an index, you use

ALTER INDEX xxx DISABLE

That doesn’t work for triggers. The ALTER TRIGGER syntax is used for changing code.

You could use ALTER TABLE on DML triggers, but not for DDL triggers. The DISABLE TRIGGER DDL can be used.

To stop tracking user logins, I can use:

DISABLE TRIGGER CatchLogins ON ALL Server
;

There is an ENABLE TRIGGER syntax as well to turn the triggers back on. These two commands allow you to save the trigger code, but have it enabled or disabled as needed.


Filed under: Blog Tagged: auditing, syndicated, T-SQL
Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.