May 26, 2010 20:41 by
AdminON 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
f23aa682-7ec3-4023-b7f0-03effd282f58|0|.0