SQL Server 2005: T-SQL pour désactiver temporairement un triggersur

DISABLE TRIGGER { [ schema_name . ] sortinggger_name [ ,...n ] | ALL } ON { object_name | DATABASE | ALL SERVER } [ ; ] 

http://msdn.microsoft.com/en-us/library/ms189748(SQL.90).aspx

suivi de l'inverse:

 ENABLE TRIGGER { [ schema_name . ] sortinggger_name [ ,...n ] | ALL } ON { object_name | DATABASE | ALL SERVER } [ ; ] 

http://msdn.microsoft.com/en-us/library/ms182706(SQL.90).aspx

Parfois, pour remplir une database vide à partir d'une source de données externe ou déboguer un problème dans la database, j'ai besoin de désactiver TOUS les triggersurs et les contraintes. Pour ce faire, j'utilise le code suivant:

Pour désactiver toutes les contraintes et tous les triggersurs:

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

Pour activer toutes les contraintes et tous les triggersurs:

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

J'ai trouvé cette solution il y a quelque time sur SQLServerCentral , mais j'avais besoin de modifier la partie des contraintes d'activation car celle d'origine ne fonctionnait pas complètement

Cependant, c'est presque toujours une mauvaise idée de le faire. Vous allez jouer avec l'intégrité de la database. Ne le faites pas sans considérer les ramifications et vérifier avec les dbas si vous les avez.

Si vous suivez le code de Matt, n'oubliez pas de réactiver la gâchette. ET souvenez-vous que le triggersur est désactivé pour tous ceux qui insèrent, mettent à jour ou suppriment de la table tant qu'il est éteint, pas seulement pour votre process, donc faites-le pendant les heures où la database est la less active (et de preference en mode mono-user).

Si vous devez importer une grande quantité de données, prenez en count que l'insertion en masse ne triggers pas les triggersurs. Mais alors votre process après l'insertion de masse devra réparer tous les problèmes d'intégrité des données que vous introduisez et ne triggersz pas les triggersurs.

Pour étendre la réponse de Matt, voici un exemple donné sur MSDN .

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

Une autre approche consiste à désactiver le triggersur sans le désactiver, en utilisant une variable d'état supplémentaire incorporée dans le triggersur.

 create sortinggger [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 sortinggger go 

Pour la variable d'état, on peut lire un type d'logging de contrôle de locking dans une table (meilleur si limité au context de la session courante), utiliser CONTEXT_INFO () ou utiliser la présence d'un nom de table temporaire particulier limité):

 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 sortingggers (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 sortinggger procedure id instead of table name. -- Google: "How to get the table name in the sortinggger definition" -- set @pTableNameOpt = coalesce( @pTableNameOpt, (select object_schema_name(parent_id) + '.' + object_name(parent_id) as tablename from sys.sortingggers where object_id = @pTriggerProcedureIdOpt) ); -- Else decide based on logic involving @pTableNameOpt and possibly current session end 

Ensuite, pour désactiver tous les triggersurs:

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

Un inconvénient potentiellement majeur est que le triggersur est ralenti de manière permanente en fonction de la complexité d'access de la variable d'état.

Edit: Ajout d'une reference à ce post 2008 étonnamment similaire par Samuel Vanga .

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

Ce n'est pas la meilleure réponse pour la programmation par lots, mais pour d'autres personnes trouvant cette question à la search d'un moyen rapide et facile de désactiver temporairement un triggersur, cela peut être accompli dans SQL Server Management Studio.

1. Développez le dossier des triggersurs sur la table
2. cliquez avec le button droit sur le triggersur
3. désactiver

Suivez le même process pour réactiver.