SQL Server 2005 Tidbit 034

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;
GO
DISABLE TRIGGER safety ON DATABASE;
GO
ENABLE 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.