Trigger INSTEAD OF UPDATE

Version imprimable

06/10/2015, 11h16
Garhein

Trigger INSTEAD OF UPDATE

Bonjour,

Je rencontre le besoin de mettre en place un trigger INSTEAD OF UPDATE sur l'une de mes tables et je me pose des questions sur le fonctionnement d'un tel trigger.

Un ordre UPDATE peut mettre à jour plusieurs lignes de la table en une fois, via des conditions dans la clause WHERE. Qu'en est-il dans le trigger ?

Code:

UPDATE [dbo].[MaTable] SET MonChamp = 'MaValeur' WHERE MaColonne = 'A'

La condition (qui dans certains cas peut être plus complexe) appliquée à cet ordre SQL peut-elle être récupérée et appliquée dans le trigger ?

La mise en oeuvre de ce trigger est obligatoire car le traitement complémentaire effectué par celui-ci ne peut être fait par aucune autre manière (du fait du fonctionnement de notre outil de développement).

Merci par avance pour vos réponses.
06/10/2015, 11h46
rudib

Bonjour,

Le trigger est ensembliste en SQL Server. Donc un seul déclenchement sur un UPDATE multi-lignes.
Tu ne peux pas récupérer la condition, mais tu as deux tables virtuelles : INSERTED pour les nouvelles valeurs, DELETED pour les anciennes.
Ton trigger INSTEAD OF ne fait rien dans la table. A toi de récupérer les données avec une requête ensembliste sur INSERTED pour effectuer l'UPDATE que tu veux.

Sûr que tu dois passer par un trigger INSTEAD OF et pas un trigger AFTER ?

Nous souhaitons mettre en place l'intégrité référentielle (celle-ci n'étant pas gérée sur le moteur de base de données utilisé précédemment) sur nos tables mais rencontrons des soucis car certaines des foreign key sont de type VARCHAR.

Dans notre outil de développement, nous avons à notre disposition une constante permettant d'affecter NULL à une variable. Cependant lors de l'exécution de la requête d'insertion ou de mise à jour, le NULL est remplacé par une chaîne vide. Du coup on obtient une violation d'intégrité car la clé chaîne vide n'existe pas dans la table de référence. Ce trigger nous permettrait d'affecter NULL à la colonne en question si la valeur indiquée est une chaîne vide.

Un trigger AFTER ne peut pas convenir car celui-ci est déclenché, reprends moi si je me trompe, après l'exécution et donc après la vérification de l'intégrité référentielle.

En insertion le trigger INSTEAD OF INSERT semble bien fonctionner. Il a été écrit de la manière suivante :
Code:

1 2 3 4 5 6 7 8 9 10 11 CREATE TRIGGER [dbo].[TG_IB_INSERT] ON [dbo].[S_P_PROLONG] INSTEAD OF INSERT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements. SET NOCOUNT ON; INSERT INTO S_P_PROLONG ( PROLONG_CODE, MAPR_CODE ) SELECT PROLONG_CODE, IIF(MAPR_CODE = '', NULL, MAPR_CODE) FROM inserted END

Tu ne te trompes pas. Je comprends mieux.

Suggestions pour le trigger :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TRIGGER [dbo].[TG_IB_INSERT] ON [dbo].[S_P_PROLONG] INSTEAD OF INSERT
AS
BEGIN
        IF @@ROWCOUNT = 0 RETURN;
	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;
 
	INSERT INTO S_P_PROLONG ( PROLONG_CODE, MAPR_CODE )
	           SELECT PROLONG_CODE, NULLIF(MAPR_CODE,'')
	           FROM    inserted
END

Merci pour les suggestions !

Pour les mises à jour de données, le trigger suivant fonctionne bien. Mais est-il correct et peut-il y avoir des cas où celui-ci ne fonctionne pas ?

Le champ "PROLONG_CODE" correspond à la clé primaire mise en place dans la table.
Je n'ai mis dans l'exemple ci-dessous que 3 colonnes mais la table en compte une dizaine et l'ordre de mise à jour peut porter sur la donnée d'une seule colonne.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 CREATE TRIGGER [dbo].[TG_IB_UPDATE] ON [dbo].[S_P_PROLONG] INSTEAD OF UPDATE AS BEGIN IF @@ROWCOUNT = 0 RETURN; -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements. SET NOCOUNT ON; UPDATE S_P_PROLONG SET PROLONG_CODE = i.PROLONG_CODE, MAPR_CODE = NULLIF(i.MAPR_CODE,''), PROLONG_DDEBU = i.PROLONG_DDEBU, FROM ( SELECT PROLONG_CODE, MAPR_CODE, PROLONG_DDEBU FROM inserted ) i WHERE i.PROLONG_CODE = S_P_PROLONG.PROLONG_CODE END
J'en demande peut être beaucoup mais ça n'est pas un sujet que je maîtrise (sinon je ne serai pas ici).

à première vue ça me paraît plutôt pas mal. Tu peux aussi utiliser une syntaxe spécifique à T-SQL (ma préférence mais c'est moins portable) qui te permet de spécifier une jointure dans l'UPDATE.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 CREATE TRIGGER [dbo].[TG_IB_UPDATE] ON [dbo].[S_P_PROLONG] INSTEAD OF UPDATE AS BEGIN IF @@ROWCOUNT = 0 RETURN; -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements. SET NOCOUNT ON; UPDATE p SET PROLONG_CODE = i.PROLONG_CODE, MAPR_CODE = NULLIF(i.MAPR_CODE,''), PROLONG_DDEBU = i.PROLONG_DDEBU, FROM inserted i JOIN S_P_PROLONG p ON i.PROLONG_CODE = p.PROLONG_CODE END

06/10/2015, 17h01
Garhein

Ta façon de faire est moins portable mais simplifie la relecture, du moins à mon avis.

En tout cas merci beaucoup pour tes réponses, ça m'a permis de bien avancer sur le sujet !