SQL Server DDL триггер на удаленной таблице

У меня есть база данных с несколькими таблицами: tab1, tab2, tab3. Как создать триггер DDL для таблицы удаления только для tab2 (не для tab1 и tab3). когда drop tab2 Мне нужно обновить значения в этой таблице, но не удалять tab2. Как я могу это сделать? Я нашел это, но не понимаю, как это работает:

create trigger trDatabse_OnDropTable
on database
for drop_table
as
begin
 set nocount on;
 select
  'Table dropped: ' +
  quotename(eventdata().value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname')) + N'.' +
  quotename(eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'));
end;

Спасибо!

3 ответа

Решение

В этой статье триггеры DDL вы обнаружите, что триггеров INSTEAD OF DDL нет, как в триггерах DML, поэтому вы не можете предотвратить их сброс и выполнить собственную команду:

Триггеры DDL срабатывают только после запуска операторов DDL, которые их запускают. Триггеры DDL нельзя использовать как триггеры INSTEAD OF. Триггеры DDL не запускаются в ответ на события, которые влияют на локальные или глобальные временные таблицы и хранимые процедуры.

Этот фрагмент кода, который вы разместили, предназначен для регистрации события DROP TABLE. eventdata() содержит XML с некоторой информацией о событии: LoginName, ObjectName, TSQLCommand, PostTime и многие другие.

Вы можете предотвратить удаление таблицы с помощью триггера ddl. Что-то вроде:

CREATE TRIGGER drop_safe 
ON DATABASE 
FOR DROP_TABLE 
AS 
   PRINT 'You must disable Trigger "drop_safe" to drop table!' 
   ROLLBACK
;

Чтобы предотвратить удаление только из указанной таблицы, вы должны смотреть на eventdata() и ROLLBACK только в этом особом случае.

create trigger trDatabse_OnDropTable
on database
for drop_table
as
begin
    set nocount on;

    --Get the table schema and table name from EVENTDATA()
    DECLARE @Schema SYSNAME = eventdata().value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    DECLARE @Table SYSNAME = eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname');

    IF @Schema = 'dbo' AND @Table = 'tab2'
    BEGIN
        PRINT 'DROP TABLE Issued.';

        --Optional: error message for end user.
        RAISERROR ('[dbo].[tab2] cannot be dropped.', 16, 1);

        --Rollback transaction for the DROP TABLE statement that fired the DDL trigger
        ROLLBACK;

        --Run your update after the ROLLBACK
        BEGIN TRAN
            UPDATE dbo.tab2
            SET ... ;
        COMMIT;
    END
    ELSE
    BEGIN
        --Do nothing.  Allow table to be dropped.
        PRINT 'Table dropped: [' + @Schema + '].[' + @Table + ']';
    END
end;
Другие вопросы по тегам