SQL Server 2005: T-SQL для временного отключения триггера

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

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

Источник

7 ответов

Решение
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, но мне нужно было изменить часть разрешающих ограничений, так как оригинальная не работала полностью

Источник

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

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

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

Источник

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

USE AdventureWorks;
GO
DISABLE TRIGGER Person.uAddress ON Person.Address;
GO
ENABLE Trigger Person.uAddress ON Person.Address;
GO
Источник 
ALTER TABLE table_name DISABLE TRIGGER TRIGGER_NAME
-- Here your SQL query
ALTER TABLE table_name ENABLE TRIGGER TRIGGER_NAME
Источник

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

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 года Самуэля Ванга.

Источник

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

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

Выполните тот же процесс, чтобы снова включить.

Источник
Другие вопросы по тегам