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

Question

Est-il possible de désactiver un déclencheur pour un lot de commandes puis de le réactiver une fois le lot terminé?

Je suis sûr que je pourrais supprimer le déclencheur et le réajouter mais je me demandais s'il y avait une autre façon.

Answer 1

DÉSACTIVER DÉCLENCHEUR { [ nom_schema . ] nom_déclencheur [ ,...n ] | TOUS }
SUR { nom_objet | BASE DE DONNÉES | TOUS SERVEUR } [ ; ]

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

suivi par l'inverse :

ACTIVER DÉCLENCHEUR { [ nom_schema . ] nom_déclencheur [ ,...n ] | TOUS }
SUR { nom_objet | BASE DE DONNÉES | TOUS SERVEUR } [ ; ]

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

Answer 2

Parfois, pour peupler une base de données vide à partir d'une source de données externe ou déboguer un problème dans la base de données, je dois désactiver TOUS les déclencheurs et contraintes. Pour ce faire, j'utilise le code suivant :

Pour désactiver toutes les contraintes et déclencheurs :

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

Pour activer toutes les contraintes et déclencheurs :

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 temps sur SQLServerCentral, mais j'ai dû modifier la partie activation des contraintes car l'originale ne fonctionnait pas entièrement

Answer 3

Cependant, il est presque toujours une mauvaise idée de le faire. Vous allez perturber l'intégrité de la base de données. Ne le faites pas sans considérer les conséquences et vérifier auprès des dbas si vous les avez.

Si vous suivez le code de Matt, assurez-vous de penser à réactiver le déclencheur. Et rappelez-vous que le déclencheur est désactivé pour tout le monde insérant, mettant à jour ou supprimant de la table pendant qu'il est éteint, pas seulement pour votre processus, donc s'il doit être fait, faites-le pendant les heures où la base de données est moins active (et de préférence en mode monoutilisateur).

Si vous avez besoin de le faire pour importer une grande quantité de données, alors considérez que l'insertion en bloc ne déclenche pas les déclencheurs. Mais ensuite, votre processus après l'insertion en bloc devra corriger tout problème d'intégrité des données que vous introduisez en ne déclenchant pas les déclencheurs.

Answer 4

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

Answer 5

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

create trigger [SomeSchema].[SomeTableIsEditableTrigger] SUR [SomeSchema].[SomeTable]
pour insert, update, delete 
comme
déclarer
    @isTableTriggerEnabled bit;

exec usp_IsTableTriggerEnabled -- Doit utiliser USP au lieu de UFN pour accéder à #temp
    @pTriggerProcedureIdOpt  = @@procid,    
    @poIsTableTriggerEnabled = @isTableTriggerEnabled out;

si (@isTableTriggerEnabled = 0)
    retour;

-- Reste du déclencheur existant
go

Pour la variable d'état, on pourrait lire un type d'enregistrement de contrôle de verrou dans une table (idéalement limité au contexte de la session en cours), utiliser CONTEXT_INFO(), ou utiliser la présence d'un nom de table temporaire particulier (déjà limité à la portée de la session) :

create proc [usp_IsTableTriggerEnabled]
    @pTriggerProcedureIdOpt  bigint          = null, -- Fournir ceci
    @pTableNameOpt           varchar(300)    = null, -- ou cela
    @poIsTableTriggerEnabled bit             = null out
début

    set @poIsTableTriggerEnabled = 1; -- Valeur de retour par défaut (s'assurer que ce n'est pas nul)

    -- Autoriser une session particulière à désactiver tous les déclencheurs (puisque les 
    -- tables temporaires locales sont limitées à la portée de la session).
    --
    si (object_id('tempdb..#Common_DisableTableTriggers') n'est pas null)
    begin
        set @poIsTableTriggerEnabled = 0;
        retour;
    end

    -- Résoudre le nom de la table si l'identifiant de procédure de déclenchement est donné au lieu du nom de table.
    -- Google : "Comment obtenir le nom de la table dans la définition du déclencheur"
    --
    set @pTableNameOpt = coalesce(
         @pTableNameOpt, 
         (select object_schema_name(parent_id) + '.' + object_name(parent_id) as tablename 
           from sys.triggers 
           where object_id = @pTriggerProcedureIdOpt)
    );

    -- Sinon décider en fonction de la logique impliquant @pTableNameOpt et éventuellement de la session en cours
end

Ensuite, pour désactiver tous les déclencheurs :

sélectionner 1 comme A dans #Common_DisableTableTriggers;
-- faire le travail 
supprimer la table #Common_DisableTableTriggers; -- ou fermer la connexion

Un inconvénient potentiel majeur est que le déclencheur est ralenti en permanence en fonction de la complexité de l'accès à la variable d'état.

Édit: Ajout d'une référence à ce post de 2008 étonnamment similaire de Samuel Vanga.