CODEWITHSUNDEEP
sql-server-2008triggersddl-trigger
user1300900
user1300900

Reputation: 11

How to prevent only non-empty tables from being dropped using a DDL trigger?

I want to prevent dropping table if there are rows in it.

I wrote:

create trigger prevDrop on database for drop_table
as
begin
   if exists (select * from dropped_table)
    raiserror('cant do',25,1)
end

But I am getting a syntax error with the dropped table.

How can I trace which table is about to be deleted?

Upvotes: 1

Views: 1253

Answers (1)

anon
anon

Reputation:

I don't think you can do this with a DDL trigger, since in this case it is an after trigger - so the table no longer exists, and there is no trace of it in the system metadata. Why not just prevent dropping ALL tables, not just non-empty ones?

CREATE TRIGGER prevDrop ON DATABASE
FOR DROP_TABLE
AS
BEGIN
  ROLLBACK;
  RAISERROR('Disable the trigger prevDrop to drop tables!',11,1);
END
GO

What they should implement is INSTEAD OF DDL triggers - please vote for it here:

http://connect.microsoft.com/SQLServer/feedback/details/243986

I also had them change the documentation, which originally was misleading people into believing that DDL triggers prevented actions, when really it rolls them back:

http://connect.microsoft.com/SQLServer/feedback/details/752210

The reason I am telling you all of this is because you state:

How can I trace which table is about to be deleted?

However this implies that you think the table hasn't been deleted yet. It has. You can certainly get the table name within the DDL trigger:

DECLARE @e XML = EVENTDATA(), @t NVARCHAR(513);

SET @t = @e.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(255)');
   + '.' + @e.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(255)');

RAISERROR('%s has been dropped.', 11, 1, @t);

But this doesn't help you. You can't check the contents of the table because it no longer exists. You can try in vain to do something clever, like:

DECLARE @sql NVARCHAR(MAX) = N'SELECT COUNT(*) FROM ' + @t;
EXEC sp_executesql @sql;

But this will just yield:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.tablename'.

Even though, if you rollback in the trigger, or the transaction is otherwise aborted, the table exists again right afterward. As far as the trigger itself is concerned, it doesn't exist.

Upvotes: 2

Related Questions