SQL Server 2005: T-SQL para deshabilitar temporalmente un disparador

A veces, para rellenar una base de datos vacía desde una fuente de datos externa o depurar un problema en la base de datos, necesito deshabilitar TODOS los disparadores y restricciones. Para ello utilizo el siguiente código:

Para deshabilitar todas las restricciones y disparadores:

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

Para habilitar todas las restricciones y disparadores:

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

Encontré esa solución hace algún tiempo en SQLServerCentral , pero necesitaba modificar la parte habilitar restricciones ya que la original no funcionaba completamente

Sin embargo, casi siempre es una mala idea hacer esto. Te meterás con la integridad de la base de datos. No lo haga sin considerar las ramificaciones y verificar con los dba si las tiene.

Si sigues el código de Matt asegúrate de recordar volver a encender el gatillo. Y recuerde que el disparador está desactivado para todos los que insertan, actualizan o eliminan de la tabla mientras está desactivado, no solo para su proceso, por lo que si debe hacerse, hágalo durante las horas cuando la base de datos está menos activa (y preferiblemente en modo de usuario único).

Si necesita hacer esto para importar una gran cantidad de datos, considere que la inserción masiva no dispara los disparadores. Pero luego su proceso después de la inserción masiva tendrá que solucionar cualquier problema de integridad de datos que introduzca al no disparar los disparadores.

Para extender la respuesta de Matt, aquí hay un ejemplo dado en MSDN.

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

Otro enfoque es desactivar efectivamente el disparador sin desactivarlo realmente, utilizando una variable de estado adicional que se incorpora en el disparador.

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

Para la variable de estado se podría leer algún tipo de registro de control de bloqueo en una tabla (mejor si está limitado al contexto de la sesión actual), usar CONTEXT_INFO (), o usar la presencia de un nombre de tabla temporal particular (que ya está limitado al alcance de la sesión):

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

Entonces para desactivar todo desencadenantes:

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

Un inconveniente potencialmente importante es que el disparador se ralentiza permanentemente dependiendo de la complejidad del acceso a la variable de estado.

Editar: Añadiendo una referencia a este increíblemente similar post de 2008 de Samuel Vanga.

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

No es la mejor respuesta para la programación por lotes, pero para otros que encuentran esta pregunta en busca de una forma rápida y fácil de desactivar temporalmente un disparador, esto se puede lograr en SQL Server Management Studio.

1. expanda la carpeta triggers en la tabla
2. haga clic con el botón derecho en el disparador
3. desactivar

Siga el mismo proceso para volver a habilitar.