Multi row trigger.

Version imprimable

Voir 40 message(s) de cette discussion en une page

Multi row trigger.

Bonjour à tous.
J'ai un problème lors de mes udates/deletes sur mes déclencheurs.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 IF @EtatTrigger = 2 -- Dans le cas d'un Update. BEGIN SET @MyReqInstruction = 'UPDATE MyTable ' SET @MyReqValue = 'SET ' If (SELECT CHAMP1 FROM DELETED) <> (SELECT CHAMP1 FROM INSERTED) BEGIN IF len(@MyReqValue) > 4 SET @MyReqValue = (@MyReqValue + ', ') SET @MyReqValue += ('CHAMP1 = ' + QUOTENAME((SELECT CHAMP1 FROM INSERTED),'''')) END If (SELECT CHAMP2 FROM DELETED) <> (SELECT CHAMP2 FROM INSERTED) BEGIN IF len(@MyReqValue) > 4 SET @MyReqValue = (@MyReqValue + ', ') SET @MyReqValue += ('CHAMP2 = ' + QUOTENAME((SELECT CHAMP2 FROM INSERTED),'''')) END If (SELECT CHAMP3 FROM DELETED) <> (SELECT CHAMP3 FROM INSERTED) BEGIN IF len(@MyReqValue) > 4 SET @MyReqValue = (@MyReqValue + ', ') SET @MyReqValue += ('CHAMP3 = ' + QUOTENAME((SELECT CHAMP3 FROM INSERTED),'''')) END ... ... ... END
Enfait, lorsque mon déclencheur capte une requête, je veux la réécrire (car impossible à capter directement si elle provient d'une requête paramétré..) et la stocker dans une autre table.

Cependant, dans le cas ou il y'a un Update sur plusieurs rows (une seule instruction donc) cela pose problème.

J'aimerais donc idéalement faire un "for each" visiblement impossible en sql et ne tiens pas à utiliser les curseurs.

Mes recherches se sont révélés infructueuses, si quelqu'un à une idée..
Merci à vous.

A+

06/02/2014, 11h19
amedee

Bonjour,

Dans ce cas, dans le trigger, il faut trouver un moyen pour réaliser le traitement de manière ensembliste, si possible en une seule requête et des CASE WHEN.

Cordialement.
06/02/2014, 11h41
SQLpro

Que cherchez-vous à faire au juste ???

A +
06/02/2014, 11h55
egautier18

Bonjour,
Merci à vous de répondre.

Enfait ce que j'aimerais c'est :

Si j'ai 3 update sur MaTable1
Mon triggerTable1 écris 3 requêtes updates dans MaTableLOG avec d'autre info (Ref Clef primaire, DateRequete, NomTable, TypeInstruction...)

(Et je dois également écrire une requêtes avec uniquement les champs qui sont modifiés)..

Si j'ai une requête qui update 1 row. Tout vas bien..
Si j'ai une requête qui update X rows. Tout vas mal..

Pour compléter ce que j'ai dit :
dans le trigger, il faut faire une seule requête avec des CASE WHEN et des jointures sur INSERTED et DELETED

Voilà un exemple à adapter à votre situation :

Code:

1
2
3
4
5
6
7
8
 
UPDATE MaTable 
SET MaTableChampX = CASE WHEN INSERTED.MaTableChampX = 1 THEN MaValeur ELSE 0 END           
FROM MaTable
INNER JOIN INSERTED ON INSERTED.MaTableChamp1 = MaTable.MaTableChamp1
INNER JOIN DELETED ON DELETED.MaTableChamp1 = MaTable.MaTableChamp1
INNER JOIN Table2 ON INSERTED.Table2Champ1 = Table2.Table2Champ1 
WHERE DELETED.Table2Champ1 <> INSERTED.Table2Champ1

Je ne sais pas si je passe complètement à côté ou si cela n'est pas adaptable mais je ne vois pas du tout comment m'inspirer de la requête que vous m'avez donné afin d'avoir en résultat des requêtes écrites (non exécutée, mais stocké dans un champ)

Et je ne vois pas non plus sur quel critère je peux boucler afin de jouer sur mes X enregistrements..

J'avais pensé à jouer avec ROW_NUMBER() mais il ne me sort pas vraiment ce à quoi je m'attendais..
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 DECLARE @MyRowCount int = 3 DECLARE @UpUp int = 1 WHILE (@UpUp <> @MyRowCount) BEGIN SET @MyRequete = 'DELETE MyTable WHERE CHAMP1 = ' + QUOTENAME((SELECT CHAMP1 FROM DELETED where (SELECT ROW_NUMBER() OVER (ORDER BY CLEF1, CLEF2, CLEF3, CLEF4) AS rownumber FROM DELETED) = @UpUp),'''') .. .. .. SET @UpUp +=1 END
Si @UpUp = 1, il me récupère toutes les rows de la table (donc bug de nouveau),
Si il est > 1, il ne trouve aucun résultat..

[EDIT]
Désolé, je vous ai mis mes test dans le cas d'un DELETE, avec une seule table temporaire à gérer, dans un premier temps..

En reprenant votre exemple, visiblement il y a 3 colonnes dans la table. Il doit y avoir aussi une clé unique que j'appellerai MaCle.

Pour le trigger ON UPDATE : 3 requêtes successives pour les trois champs.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
INSERT INTO MaTableLOG (Requete)
SELECT 'UPDATE MyTable SET CHAMP1 = ' + CONVERT(VARCHAR, INSERTED.CHAMP1) + ' WHERE MaCle = ' + CONVERT(VARCHAR, INSERTED.MaCle) AS Requete 
FROM DELETED 
INNER JOIN INSERTED ON INSERTED.MaCle = DELETED.MaCle 
WHERE DELETED.CHAMP1 <> INSERTED.CHAMP1;
 
INSERT INTO MaTableLOG (Requete)
SELECT 'UPDATE MyTable SET CHAMP2 = ' + CONVERT(VARCHAR, INSERTED.CHAMP2) + ' WHERE MaCle = ' + CONVERT(VARCHAR, INSERTED.MaCle) AS Requete 
FROM DELETED 
INNER JOIN INSERTED ON INSERTED.MaCle = DELETED.MaCle 
WHERE DELETED.CHAMP2 <> INSERTED.CHAMP2;
 
INSERT INTO MaTableLOG (Requete)
SELECT 'UPDATE MyTable SET CHAMP3 = ' + CONVERT(VARCHAR, INSERTED.CHAMP3) + ' WHERE MaCle = ' + CONVERT(VARCHAR, INSERTED.MaCle) AS Requete 
FROM DELETED 
INNER JOIN INSERTED ON INSERTED.MaCle = DELETED.MaCle 
WHERE DELETED.CHAMP3 <> INSERTED.CHAMP3;

Re,

Merci amedee.

Voila ce que cela donne dans le cas d'un Update
Code:

1 2 3 4 5 6 7 8 INSERT INTO MaTableLOG (MyVal) SELECT 'UPDATE Table1 SET' + CASE WHEN (DELETED.CHAMP1 <> INSERTED.CHAMP1) THEN ' CHAMP1 = ' + QUOTENAME((INSERTED.CHAMP1),'''') ELSE '' END + CASE WHEN (DELETED.CHAMP2 <> INSERTED.CHAMP2) THEN ' , CHAMP2 = ' + QUOTENAME((INSERTED.CHAMP2),'''') ELSE '' END + ' WHERE CHAMPCLEF = ' + QUOTENAME((INSERTED.CHAMPCLEF),'''') AS MyVal FROM DELETED INNER JOIN INSERTED ON INSERTED.CHAMPCLEF = DELETED.CHAMPCLEF
Ca marche sur ma base test, il me reste à faire des test plus poussé et à le faire pour le DELETE.

Cependant une nouvelle question se pose.

Comment gérer la ", " entre mes champs ? Car champ2 peut être le seul modifié et du coup la virgule qui le précède est inutile (même problématique)..

Si quelqu'un à une idée sur comment définir quand la mettre ..

07/02/2014, 12h20
egautier18

Bonjour,

Je reviens vers vous afin de vous dire que après mes tests tout marche parfaitement.

Pour mon problème de virgule je n'ai rien trouvé de moins lourd que d'inclure un update d'un champ clef en premier lieux (c'est le mieux que j'ai trouvé vu que certaine de mes tables comportent un certain nombre de champs..)

Encore merci.
A+

Bonjour,

Attention, avec votre solution, si une colonne passe de NULL à une valeur, on inversement, elle ne sera pas prise en compte, car alors la condition WHEN (DELETED.CHAMP1 <> INSERTED.CHAMP1) ne sera pas vraie.

concernant votre virgule, vous pourriez la mettre systématiquement (même pour CHAMP1) et faire une STUFF pour supprimer la première virgule :
Code:

1 2 3 4 5 6 7 8 INSERT INTO MaTableLOG (MyVal) SELECT STUFF( 'UPDATE Table1 SET' + CASE WHEN (DELETED.CHAMP1 <> INSERTED.CHAMP1) THEN ' ,CHAMP1 = ' + QUOTENAME((INSERTED.CHAMP1),'''') ELSE '' END + CASE WHEN (DELETED.CHAMP2 <> INSERTED.CHAMP2) THEN ' , CHAMP2 = ' + QUOTENAME((INSERTED.CHAMP2),'''') ELSE '' END + ' WHERE CHAMPCLEF = ' + QUOTENAME((INSERTED.CHAMPCLEF),''''), 19, 1, '') AS MyVal FROM DELETED INNER JOIN INSERTED ON INSERTED.CHAMPCLEF = DELETED.CHAMPCLEF

10/02/2014, 08h47
egautier18

Bonjour,
J'ai ré ouvert le sujet car en effet, après des tests, il s'avère que si mon champ passe de NULL à valeur, il n'est pas capté par <>.
J'ai du mal à comprendre, NULL est pourtant différent de "valeur" et inversement ?

Qu'elles sont mes options dans ce cas ?

Merci d'avoir soulevé le problème.

A+
10/02/2014, 09h59
aieeeuuuuu
Citation:

Envoyé par egautier18

Bonjour,
J'ai du mal à comprendre, NULL est pourtant différent de "valeur" et inversement ?

justement non !
NULL n'étant pas une valeur, il ne peut être ni égal, ni différent à quoi que ce soit, même pas à lui-même.

Voyez NULL comme "inconnu", et tout devient plus clair !

ainsi, à la question "est-ce que NULL (inconnu) est différent de 2", la réponse est : "NULL (inconnu)"

Le mieux serait donc de traiter ce cas à part :
Code:

1 2 3 4 5 WHEN (DELETED.CHAMP1 <> INSERTED.CHAMP1) OR (DELETED.CHAMP1 IS NULL AND INSERTED.CHAMP1 IS NOT NULL) OR (DELETED.CHAMP1 IS NOT NULL AND INSERTED.CHAMP1 IS NULL) THEN ...

Re,

J'ai donc procédé comme ceci..
Code:

1 2 3 CASE WHEN ((DELETED.CHAMP1 <> INSERTED.CHAMP1) OR (DELETED.CHAMP1 IS NULL AND INSERTED.CHAMP1 IS NOT NULL)) THEN ' ,CHAMP1 = ' + QUOTENAME((INSERTED.CHAMP1),'''') ELSE '' END + CASE WHEN (DELETED.CHAMP1 IS NOT NULL AND INSERTED.CHAMP1 IS NULL) THEN ' ,CHAMP1 = NULL' ELSE '' END +
Je mets le sujet en résolu, si jamais vous repassez dans le coin et pensez qu'il y 'a plus propre. Je suis intéressé par toute idée d'amélioration.

Bonne journée.
A+

Je viens de voir que vous avez répondu pendant que j'écrivais.
Visiblement, je suis bien obligé de traité cela en plusieurs conditions.

Merci à vous.

11/02/2014, 10h03
amedee
Bonjour,

Même si la discussion est clause, je me permet d'ajouter une remarque concernant le traitement des NULL. On peut aussi voir à utiliser la fonction COALESCE qui permet de récupérer la première valeur non NULL de la liste passée en paramètre. C'est très efficace.

exemple : COALESCE(DELETED.CHAMP1, INSERTED.CHAMP1, '')
- Renvoie '' si DELETED.CHAMP1 et INSERTED.CHAMP1 sont NULL.
- Renvoie INSERTED.CHAMP1 si DELETED.CHAMP1 est NULL.
- Sinon renvoie DELETED.CHAMP1.

Bonjour à vous.

Je remets ce sujet au gout du jour car, une modification a du être apportée.

On m'a demandé en effet de ne pas prendre en compte les valeurs qui passent de NULL à '' (vide).
J'ai donc modifié le code mais une nouvelle problématique se pose.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 INSERT INTO MaTableLOG (MyVal, DateRequete) SELECT REPLACE( 'UPDATE Table1 SET' + CASE WHEN (DELETED.CHAMP1 <> INSERTED.CHAMP1 AND INSERTED.CHAMP1 IS NOT NULL AND INSERTED.CHAMP1 <> '') THEN ' ,CHAMP1 = ' + QUOTENAME((INSERTED.CHAMP1),'''') ELSE '' END + CASE WHEN (DELETED.CHAMP1 IS NOT NULL AND INSERTED.CHAMP1 IS NULL) THEN ' ,CHAMP1 = NULL' ELSE '' END + CASE WHEN (DELETED.CHAMP2 <> INSERTED.CHAMP2 AND INSERTED.CHAMP2 IS NOT NULL AND INSERTED.CHAMP2<> '') THEN ' ,CHAMP2 = ' + QUOTENAME((INSERTED.CHAMP2),'''') ELSE '' END + CASE WHEN (DELETED.CHAMP2 IS NOT NULL AND INSERTED.CHAMP2 IS NULL) THEN ' ,CHAMP2 = NULL' ELSE '' END + ' WHERE CHAMPCLEF = ' + QUOTENAME((INSERTED.CHAMPCLEF),''''), 'SET ,','SET ' ) AS MyVal , GetDate() AS DateRequete FROM DELETED INNER JOIN INSERTED ON INSERTED.CHAMPCLEF = DELETED.CHAMPCLEF
Voila, donc ceci fonctionne. Le problème est que maintenant je peux insérer des requêtes dans MaTableLOG qui ne veulent rien dire.
MyVal = "UPDATE TABLE1 SET WHERE...."

J'aurais donc aimé pouvoir faire un "INSERT SELECT IF" avec en condition quelque chose IF MyVal LIKE " SET WHERE " J'abandonne l'insert..

Mes connaissances en SQL étant limitées, je n'arrive pas à mettre en place ce que j'ai pu voir sur les forums.
Je m'en retourne donc vers vous, une fois de plus.

Merci.
A+

Bonjour,

Et en mettant une clause WHERE dans le SELECT, après la jointure ? Il doit y avoir moyen de filtrer les entrées incorrectes.
Code:

1 2 3 4 5 6 7 8 9 INSERT INTO MaTableLOG (MyVal, DateRequete) SELECT ... FROM DELETED INNER JOIN INSERTED ON INSERTED.CHAMPCLEF = DELETED.CHAMPCLEF --la clause WHERE ici WHERE ...
Cordialement.

27/02/2014, 15h13
egautier18

Merci de votre retour sur le sujet.

Malheureusement je ne pense pas.
Le WHERE devra être sur le champ "MyVal" qui appartient à la table MaTableLOG.

Or il ne font pas partit du SELECT.

Du coup je ne peut pas faire un

Code:

WHERE MaTableLOG.MyVal = " condition "

(erreur : L'identificateur en plusieurs parties "MaTableLOG.MyVa" ne peut pas être liée.)

Il faut travailler directement sur la chaîne de caractère. Dans la requête SELECT, MyVal est juste un alias (AS).

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
INSERT INTO MaTableLOG (MyVal, DateRequete)
SELECT REPLACE( 'UPDATE Table1 SET' +
 
CASE WHEN (DELETED.CHAMP1 <> INSERTED.CHAMP1 AND INSERTED.CHAMP1  IS NOT NULL AND INSERTED.CHAMP1  <> '') THEN ' ,CHAMP1 = ' + QUOTENAME((INSERTED.CHAMP1),'''') ELSE '' END + 
CASE WHEN (DELETED.CHAMP1 IS NOT NULL AND INSERTED.CHAMP1 IS NULL) THEN ' ,CHAMP1 = NULL' ELSE '' END +
 
CASE WHEN (DELETED.CHAMP2 <> INSERTED.CHAMP2 AND INSERTED.CHAMP2 IS NOT NULL AND INSERTED.CHAMP2<> '') THEN ' ,CHAMP2 = ' + QUOTENAME((INSERTED.CHAMP2),'''') ELSE '' END + 
CASE WHEN (DELETED.CHAMP2 IS NOT NULL AND INSERTED.CHAMP2 IS NULL) THEN ' ,CHAMP2 = NULL' ELSE '' END +
 
' WHERE CHAMPCLEF = ' + QUOTENAME((INSERTED.CHAMPCLEF),''''), 'SET  ,','SET ' ) AS MyVal 
 
, GetDate() AS DateRequete
 
FROM DELETED 
INNER JOIN INSERTED ON INSERTED.CHAMPCLEF = DELETED.CHAMPCLEF
 
WHERE 
--l'expression qui doit être insérée dans MyVal
REPLACE( 'UPDATE Table1 SET' +
CASE WHEN (DELETED.CHAMP1 <> INSERTED.CHAMP1 AND INSERTED.CHAMP1  IS NOT NULL AND INSERTED.CHAMP1  <> '') THEN ' ,CHAMP1 = ' + QUOTENAME((INSERTED.CHAMP1),'''') ELSE '' END + 
CASE WHEN (DELETED.CHAMP1 IS NOT NULL AND INSERTED.CHAMP1 IS NULL) THEN ' ,CHAMP1 = NULL' ELSE '' END +
CASE WHEN (DELETED.CHAMP2 <> INSERTED.CHAMP2 AND INSERTED.CHAMP2 IS NOT NULL AND INSERTED.CHAMP2<> '') THEN ' ,CHAMP2 = ' + QUOTENAME((INSERTED.CHAMP2),'''') ELSE '' END + 
CASE WHEN (DELETED.CHAMP2 IS NOT NULL AND INSERTED.CHAMP2 IS NULL) THEN ' ,CHAMP2 = NULL' ELSE '' END +
' WHERE CHAMPCLEF = ' + QUOTENAME((INSERTED.CHAMPCLEF),''''), 'SET  ,','SET ' ) 
--la clause
NOT LIKE '%SET WHERE%'

27/02/2014, 17h27
egautier18

Oui cela fonctionne.

J'aimerais cependant avoir des compléments d'information si cela ne vous dérange pas.

Dans mon exemple, je n'ai que deux champs.
Mais dans le cas d'une table possédant 10,20,30 ...100. champs.

Cela rallonge t'il considérablement le temps de traitement ?

N'est il pas possible de travailler sur son Alias ? (comment cela est il géré ?)

Enfait, j'ai vraiment le sentiment de lui demander de faire deux fois le même boulot et par conséquent de lui demander deux fois plus de temps.
Je trouve cela dommage de ne pas pouvoir exploiter le premier traitement et de le répéter..

Cependant je vous suis très reconnaissant d'avoir pris ,de nouveau, le temps de m'apporter une solution qui fonctionne.
Je ne voudrais pas donner l'impression d’être insatisfait. J'essaie juste d'en savoir le plus possible afin de faire quelque chose de bien.

Un grand merci.
27/02/2014, 17h45
aieeeuuuuu

Le moteur verra qu'il s'agit de la même chose, et ne la calculera pas deux fois.

Par contre, niveau lisibilité et maintenabilité, c'est en effet pas terrible.

Vous pouvez passer par un CTE, ou une sous requête en pseudo table pour ne pas réécrire plusieurs fois les même lignes.

Cependant, je m'interroge depuis le début sur l’intérêt de tout ça ?
Que comptez vous faire de ces requêtes stockées dans des tables.

SQL Server a plusieurs systèmes intégrés pour suivre les modifications effectuées sur une base. Ce serait surement plus léger que ce que vous êtes en train de faire....

Voir 40 message(s) de cette discussion en une page