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