Блог

Можно ли отключить триггер для пакета команд, а затем включить его, когда пакет будет выполнен?

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

 DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]
 

http://msdn.microsoft.com/en-us/library/ms189748(SQL.90).aspx

за этим следует обратное:

 ENABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]
 

http://msdn.microsoft.com/en-us/library/ms182706(SQL.90).aspx

Иногда, чтобы заполнить пустую базу данных из внешнего источника данных или отладить проблему в базе данных, мне нужно отключить ВСЕ триггеры и ограничения. Для этого я использую следующий код:

Чтобы отключить все ограничения и триггеры:

 sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER  all"
 

Чтобы включить все ограничения и триггеры:

 exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? ENABLE TRIGGER  all"
 

Я нашел это решение некоторое время назад на SQLServerCentral, но мне нужно было изменить часть «Включить ограничения», так как исходное решение не работало полностью

Однако делать это почти всегда плохая идея. Вы нарушите целостность базы данных. Не делайте этого, не рассмотрев последствия и не сверившись с базами данных, если они у вас есть.

Если вы будете следовать коду Мэтта, обязательно не забудьте снова включить триггер. И помните, что триггер отключен для всех, кто вставляет, обновляет или удаляет из таблицы, пока он выключен, а не только для вашего процесса, поэтому, если это необходимо сделать, делайте это в часы, когда база данных наименее активна (и предпочтительно в однопользовательском режиме).

Если вам нужно сделать это для импорта большого объема данных, учтите, что массовая вставка не запускает триггеры. Но тогда вашему процессу после массовой вставки придется устранять любые проблемы с целостностью данных, которые вы создадите, не активируя триггеры.

Чтобы расширить ответ Мэтта, вот пример, приведенный на MSDN.

 USE AdventureWorks;
GO
DISABLE TRIGGER Person.uAddress ON Person.Address;
GO
ENABLE Trigger Person.uAddress ON Person.Address;
GO
 

Другой подход состоит в том, чтобы эффективно отключить триггер, фактически не отключая его, используя дополнительную переменную состояния, которая включена в триггер.

 create trigger [SomeSchema].[SomeTableIsEditableTrigger] ON [SomeSchema].[SomeTable]
for insert, update, delete 
as
declare
    @isTableTriggerEnabled bit;

exec usp_IsTableTriggerEnabled -- Have to use USP instead of UFN for access to #temp
    @pTriggerProcedureIdOpt  = @@procid,    
    @poIsTableTriggerEnabled = @isTableTriggerEnabled out;

if (@isTableTriggerEnabled = 0)
    return;

-- Rest of existing trigger
go
 

Для переменной состояния можно прочитать некоторую запись управления блокировкой в таблице (лучше всего, если она ограничена контекстом текущего сеанса), использовать CONTEXT_INFO () или использовать наличие определенного имени временной таблицы (которое уже ограничено областью действия сеанса).:

 create proc [usp_IsTableTriggerEnabled]
    @pTriggerProcedureIdOpt  bigint          = null, -- Either provide this
    @pTableNameOpt           varchar(300)    = null, -- or this
    @poIsTableTriggerEnabled bit             = null out
begin

    set @poIsTableTriggerEnabled = 1; -- default return value (ensure not null)

    -- Allow a particular session to disable all triggers (since local 
    -- temp tables are session scope limited).
    --
    if (object_id('tempdb..#Common_DisableTableTriggers') is not null)
    begin
        set @poIsTableTriggerEnabled = 0;
        return;
    end

    -- Resolve table name if given trigger procedure id instead of table name.
    -- Google: "How to get the table name in the trigger definition"
    --
    set @pTableNameOpt = coalesce(
         @pTableNameOpt, 
         (select object_schema_name(parent_id)   '.'   object_name(parent_id) as tablename 
           from sys.triggers 
           where object_id = @pTriggerProcedureIdOpt)
    );

    -- Else decide based on logic involving @pTableNameOpt and possibly current session
end
 

Затем, чтобы отключить все триггеры:

 select 1 as A into #Common_DisableTableTriggers;
-- do work 
drop table #Common_DisableTableTriggers; -- or close connection
 

Потенциально серьезным недостатком является то, что триггер постоянно замедляется в зависимости от сложности доступа к переменной состояния.

Правка: Добавление ссылки на этот удивительно похожий пост 2008 года Сэмюэля Ванги.

 ALTER TABLE table_name DISABLE TRIGGER TRIGGER_NAME
-- Here your SQL query
ALTER TABLE table_name ENABLE TRIGGER TRIGGER_NAME
 

Не лучший ответ для пакетного программирования, но для тех, кто ищет этот вопрос в поисках быстрого и простого способа временного отключения триггера, это можно сделать в среде SQL Server Management Studio.

1. разверните папку триггеры в таблице
2. щелкните правой кнопкой мыши на триггере
3. отключать

Выполните тот же процесс для повторного включения.