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;