1

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?

AdamL
  • 12,421
  • 5
  • 50
  • 74
user1300900
  • 11
  • 3

1 Answers1

2

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • thank you. I dont want to prevent all drops ! doesnt make sense to me. if I open transaction , will it help to solve the problem that you mentioned ? – user1300900 Feb 06 '13 at 23:52
  • Why is dropping a table okay only if nobody has inserted any data yet? – Aaron Bertrand Feb 06 '13 at 23:54
  • I don't understand your question - `DROP TABLE` is an implicit transaction, and the object is still dropped before the trigger fires, such that you can't check if it was empty or not, even though aborting the trigger essentially rolls back the drop. In any case, even if this were helpful, if you are trying to prevent other people from dropping tables stupidly, how do you expect to enforce that they always surround their forbidden `DROP TABLE` statements with a nice transaction that prevents them from running? Why not just ask them to check if the table is empty themselves? – Aaron Bertrand Feb 07 '13 at 00:01