Kevin Remde's IT Pro Weblog
SQL Server 2005 Tidbit 034
The following question was asked at a Recent “Best of SQL Server 2005 Launch” TechNet Event in Kansas City (Overland Park Kansas, actually. Had a lovely time there, thank you! …and although it was a bit of a dive, the Karaoke at the Red Balloon was a lot of fun!)
“DDL Triggers… Do you have to delete them to get rid of them? Can you simply disable and later re-enable them?”
Glad you asked, because I didn’t know, and now I do. Yes, that would certainly make sense. And indeed, it’s as simple as using the Transact SQL (T-SQL) commands DISABLE TRIGGER and ENABLE TRIGGER. Here’s some DDL Trigger sample code from the Books Online…
CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You must disable Trigger "safety" to drop or alter tables!' ROLLBACK;GODISABLE TRIGGER safety ON DATABASE;GOENABLE TRIGGER safety ON DATABASE;GO
The same holds true (though the syntax is different) for DML Triggers as well.
(NOTE: You’ll need to have SQL Server 2005 Books Online installed for the TRIGGER links above to work. I highly recommend it! It’s FREE!)
Got an IT question? Give me a comment, or contact me.