Как предотвратить удаление только непустых таблиц с помощью триггера DDL?

Question

Я хочу предотвратить сброс таблицы, если в ней есть строки.

Я написал:

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

Но я получаю синтаксическую ошибку с dropped table,

Как я могу проследить, какая таблица будет удалена?

Answer 1

Я не думаю, что вы можете сделать это с помощью триггера DDL, так как в этом случае это триггер после - поэтому таблица больше не существует, и ее нет в системных метаданных. Почему бы просто не предотвратить сброс ВСЕХ таблиц, а не только непустых?

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

То, что они должны реализовать, это INSTEAD OF DDL triggers - пожалуйста, проголосуйте за это здесь:

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

Я также заставил их изменить документацию, которая первоначально вводила людей в заблуждение, заставляя их поверить, что DDL запускает запрещенные действия, когда это действительно откатывает их назад:

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

Причина, по которой я вам все это рассказываю, заключается в том, что вы заявляете:

Как я могу проследить, какая таблица будет удалена?

Однако это означает, что вы думаете, что таблица еще не была удалена. Она имеет. Конечно, вы можете получить имя таблицы в триггере DDL:

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);

Но это тебе не поможет. Вы не можете проверить содержимое таблицы, потому что она больше не существует. Вы можете тщетно попытаться сделать что-нибудь умное, например:

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

Но это просто даст:

Сообщение 208, Уровень 16, Состояние 1, Строка 1
Неверное имя объекта 'dbo.tablename'.

Даже если вы откатите в триггере или транзакция будет прервана иным образом, таблица сразу же снова появится. Что касается самого триггера, его не существует.