SQL Server 2005:T-SQL暂时禁用触发器

是否可以禁用一批命令的触发器,然后在批处理完成后启用它？

我确信我可以放下触发器并重新添加它,但我想知道是否还有其他方法.

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,请不要这样做.

如果您遵循马特的代码,请务必记住重新打开触发器.并且记住,每个人在关闭表时插入,更新或删除表都会禁用触发器,而不仅仅是因为你的进程,所以如果必须这样做,那么在数据库最不活跃的时候这样做(最好是在单用户模式下).

如果您需要执行此操作以导入大量数据,请考虑批量插入不会触发触发器.但是,批量插入后的过程将不得不修复您引入的任何数据完整性问题,也不必触发触发器.

为了扩展Matt的答案,这是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

一个潜在的主要缺点是触发器会根据访问状态变量的复杂性而永久性地减慢.

编辑:添加对Samuel Vanga这篇惊人相似的2008年帖子的引用.