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

然而,这样做几乎总是一个坏主意。您将破坏数据库的完整性。如果没有考虑到后果并与DBA进行核对(如果有的话),就不要这样做。

如果你真的按照马特的指令去做,一定要记得重新打开扳机。请记住,触发器在关闭时对每个插入、更新或从表中删除的人禁用,而不仅仅是对您的进程禁用,因此,如果必须这样做,请在数据库最不活跃的时间内执行(最好是在单用户模式下)。

为了扩展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

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

2008 post by Samuel Vanga

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

对于批处理编程来说,这不是最好的答案,但是对于其他在搜索临时禁用触发器的快速简便方法时发现这个问题的人来说,这可以在SQLServerManagementStudio中完成。

2. 右键单击触发器
3. 使残废

按照相同的过程重新启用。