Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Disabling DDL Triggers

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

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...