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

Ответ 1

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

Ответ 2

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

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

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

Ответ 3

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

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

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

Ответ 4

Чтобы продлить ответ Matt, вот пример, приведенный в MSDN.

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

Ответ 5

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

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() или используйте присутствие определенного имени таблицы temp (которое уже ограниченный объем сеанса):

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 сообщение от Samuel Vanga.

Ответ 6

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

Ответ 7

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

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

Следуйте тому же самому процессу, чтобы снова включить.