Roman's Weekly SQL Server Tip - How to protect tables from being deleted, Part 2
A few months ago I wrote a tip showing you how you can make sure that a table doesn't get dropped by accident. At the time it generated a lot of feedback and comments in other blogs. The solution involved creating a dummy view using the "WITH SCHEMABINDING" clause. That solution is workable but it is somewhat kludgy, even though you can automate it if you wish so..
Today I want to show you how you can do the same thing in SQL Server 2005 much more elegantly using a DDL trigger. These triggers are new to SQL Server 2005, they allow you to capture almost 90 DDL events either at the database level or at the server level. In this particular case all you need to do is to create a database level DDL trigger, specify the "DROP_TABLE" event and then use XQuery to extract the table name from the EVENTDATA:
CREATE TRIGGER NoTableDrop
ON DATABASE
FOR DROP_TABLE
AS
DECLARE @data XML
SET @data = EVENTDATA()
-- check table name
IF @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)') = N'MyPreciousTable'
BEGIN
PRINT 'You can''t drop this table!!!'
ROLLBACK
END