ON DATABASE FOR DROP_TABLE AS begin /*Anthony Oct 2009 My cheesy attempt at preventing tables from being dropped accidently This logic only allows a table to be dropped if it can find another table with the same schema/name plus the Tmp_ prefix, this is how SQL Server Management Studio alters tables */ declare @data XML declare @table as varchar(100), @tmpTable as varchar(100) set @data = EVENTDATA() set @table = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(2000)') + '.' + @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)') set @tmpTable = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(2000)') + '.Tmp_' + @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)') --select @table as tableName,@tmpTable as Tmp_TableName if not exists (select 1 from sys.objects o join sys.schemas s on o.schema_id = s.schema_id where o.type = 'U' and s.name + '.' + o.name = @tmpTable) begin RAISERROR ('Are you freak''in crazy, you cannot drop tables in this database! Go talk to Anthony!', 10, 1); ROLLBACK; end end