sql-server - eliminar - disable trigger temporarily sql server

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

Question

¿Es posible desactivar un disparador para un lote de comandos y luego habilitarlo cuando el lote está listo?

Estoy seguro de que podría soltar el gatillo y volver a agregarlo, pero me preguntaba si había otra manera.

Answer 1

A veces para completar 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 desencadenantes y restricciones. Para hacerlo, uso 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 de restricciones de habilitación ya que la original no funcionaba completamente

Answer 2

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

1. expanda la carpeta de disparadores en la mesa
2. haga clic derecho en el disparador
3. inhabilitar

Sigue el mismo proceso para volver a habilitar.

Answer 3

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

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 puede leer algún tipo de registro de control de bloqueo en una tabla (mejor si se limita al contexto de la sesión actual), usar CONTEXT_INFO () o usar la presencia de un nombre de tabla temporal particular (que ya es un alcance de sesión limitado):

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

Luego, para deshabilitar todos los desencadenantes:

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

Una desventaja potencialmente importante es que el desencadenador se ralentiza permanentemente dependiendo de la complejidad del acceso a la variable de estado.

Editar: Añadiendo una referencia a esta publicación increíblemente similar de 2008 de Samuel Vanga .

Answer 4

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

Answer 5

Sin embargo, casi siempre es una mala idea hacer esto. Te meterás con la integridad de la base de datos. No lo hagas sin considerar las ramificaciones y consultar con el dbas si los tienes.

Si sigues el código de Matt, asegúrate de recordar volver a activar el gatillo. Y recuerde que el desencadenante está desactivado para todos insertando, actualizando o borrando de la tabla mientras está apagado, no solo para su proceso, por lo que si debe hacerse, hágalo durante las horas en que 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 desencadena los desencadenadores. Pero luego su proceso posterior a la inserción masiva tendrá que solucionar cualquier problema de integridad de datos que introduzca al activar los desencadenantes.

Answer 6

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

Answer 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

seguido por el inverso:

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