[SQL SERVER 2005]problème requête dans un trigger

Version imprimable

26/02/2010, 10h15
Kropernic

[SQL SERVER 2005]problème requête dans un trigger

Hello,

Dans un trigger d'update, je fais ceci :

Code:

cast((select stuff((select * from deleted for xml path('')), 1, 1, '')) as varchar(max))

Cela fonctionne parfaitement sauf que si j'update plusieurs lignes en une seule requête, j'aurais bien du xml mais j'aurais tous les records contenu dans deleted au lieu d'une.

Je me rends compte en écrivant que le problème se situe peut-être ailleurs... Il me semblait avoir lu que lors d'une requête d'update, le trigger se déclenchait pour chaque record qui subit l'update. Or apparemment, il ne se déclenche qu'une seule fois. D'où mon problème décrit plus haut... J'aurais besoin que le trigger se déclenche pour chaque ligne et qu'il n'en traite qu'une à la fois.

Que dois-je faire ???

Griftou.
26/02/2010, 13h19
jero44

Oui en SQL Server les triggers s'exécute au niveau de l'instruction pas de la ligne. c'est à dire que si votre instruction UPDATE porte sur 10 ligne le déclencheur va être exécuté 1 fois. La table DELETED contiens les lignes avant mise à jour. Il faut utiliser un curseur pour traiter les informations présentent dedans.

Code:

1
2
3
4
5
6
7
SELECT
	Val
FROM deleted
CROSS APPLY (
	SELECT cast((SELECT deleted.* FOR xml path('')) AS  varchar(max))
) AS EXT(Val)

26/02/2010, 13h46
elsuket

Bonjour,

Citation:

J'aurais besoin que le trigger se déclenche pour chaque ligne et qu'il n'en traite qu'une à la fois

Quelle différence avec leur traitement dans leur ensemble ?

Les curseurs sont à éviter à tout prix, comme je le montre ici, et de surcroit dans les triggers, qui sont gourmands en ressources.

SQL est par nature un langage ensembliste.
Dès lors il vous faut oublier tout itération à travers les lignes de vos résultats.

Rien ne vous empêche de filtrer les lignes de la table virtuelle DELETED à l'aide d'un WHERE ;)

Enfin évitez l'utilisation de * (sauf dans le cas de la fonction COUNT() ou du prédicat EXISTS), car il oblige le moteur de base de données à interroger les tables de métadonnées pour connaître la structure de votre table (virtuelle ou pas ;))

@++ ;)
01/03/2010, 08h48
Kropernic
Citation:
Envoyé par Sergejack

Ne pas utiliser de curseur.

Ce code te permet de récupérer une ligne XML par ligne de deleted.

Code:

1 2 3 4 5 6 7 SELECT Val FROM deleted CROSS APPLY ( SELECT cast((SELECT deleted.* FOR xml path('')) AS varchar(max)) ) AS EXT(Val)
D'où vient ce "Val" ???
01/03/2010, 11h18
Kropernic

Citation:

Envoyé par elsuket

Bonjour,

Quelle différence avec leur traitement dans leur ensemble ?

Les curseurs sont à éviter à tout prix, comme je le montre ici, et de surcroit dans les triggers, qui sont gourmands en ressources.

SQL est par nature un langage ensembliste.
Dès lors il vous faut oublier tout itération à travers les lignes de vos résultats.

Rien ne vous empêche de filtrer les lignes de la table virtuelle DELETED à l'aide d'un WHERE ;)

Enfin évitez l'utilisation de * (sauf dans le cas de la fonction COUNT() ou du prédicat EXISTS), car il oblige le moteur de base de données à interroger les tables de métadonnées pour connaître la structure de votre table (virtuelle ou pas ;))

@++ ;)

La différence est qu'il s'agit d'un trigger d'historisation de données. J'ai besoin de savoir ce qui a été changé pour chaque record. Pas de savoir qu'on a changé 10 records d'un seul coup. Je veux donc que on update 10 records d'un coup, il y ait 10 lignes qui soient insérées dans la table où j'historise les changements.

Pour ça j'ai une table avec la liste des tables de la db avec un id correspondant et dans ma table d'historisation, j'ai ces champs-ci :

Table_Id : Int
Old_Record : text
New_Record : text

Ce que je fait est donc mettre sous forme de xml (ce n'est peut-être pas la meilleure manière de faire mais je n'ai trouvé que ça) les records de la table deleted dans Old_Record et de inserted dans New_Record.

Il me semble évident de ce fait que j'ai besoin que chaque record soit traité séparément pour pouvoir faire du tracage sur l'un ou l'autre élément bien précis...

Citation:
Envoyé par Sergejack
Code:

1 2 3 4 5 6 7 SELECT Val FROM deleted CROSS APPLY ( SELECT cast((SELECT deleted.* FOR xml path('')) AS varchar(max)) ) AS EXT(Val)
Je viens de tester en remplaçant mon code par votre requête (qui marche très bien en l'exécutant seule) mais du coup, j'ai une erreur comme quoi la sous-requête renvoie plusieurs résultat.

Ce qui en fait est normal vu qu'elle renvoie une ligne pour chaque record de la table.

Voici la requête du trigger, cela sera sûrement plus clair avec le puzzle complet (dans ce cas-ci, il s'agit du trigger on update de la table contenant les switch):
Code:

1 2 3 4 5 6 7 8 INSERT INTO S_Log.tb_Log (table_id, old_record, new_record) SELECT tbl_id, cast((select stuff((select * from deleted for xml path('')), 1, 1, '')) as varchar(max)), cast((select stuff((select * from inserted for xml path('')), 1, 1, '')) as varchar(max)) FROM S_Log,tb_Tables WHERE tbl_schema = 'dbo' AND tbl_name = 'tb_switch';
Du coup, si je met votre requête à la place, bin forcément, ça plante. Je pourrais tenter de la modifier mais le souci c'est que je ne comprend pas vraiment ce qu'elle fait ^^

01/03/2010, 13h29
Ptit_Dje

Avez vous la main sur l'ensemble de la query ?
Si oui, vous pouvez aussi regarder du coté de la clause OUTPUT.

Bonne journée
01/03/2010, 13h55
Kropernic

Oui, je peux en faire ce que je veux.

La clause OUTPUT ? Encore un truc que je ne connais pas. (je n'ai que les bases en sql)

Je vais voir ce que mon ami google peut m'en apprendre mais si jamais vous avez un lien sous la main, je suis preneur.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
INSERT INTO S_Log.tb_Log (table_id, old_record, new_record)
SELECT tbl_id, DEL.Val, INS.Val
FROM tb_Tables
CROSS JOIN inserted
INNER JOIN deleted on (
 deleted.id = inserted.id
)
CROSS APPLY (
	SELECT cast((SELECT inserted.* FOR xml path('')) AS  varchar(max))
) AS INS(Val)
CROSS APPLY (
	SELECT cast((SELECT deleted.* FOR xml path('')) AS  varchar(max))
) AS DEL(Val)
 WHERE tbl_schema = 'dbo'
 AND tbl_name = 'tb_switch';

01/03/2010, 14h25
Kropernic

Faut vraiment que mon boss me paie cette @#!è^§ de formation sql server (et sql) pcq je n'aurais jamais été capable de pondre ce genre de truc tout seul....

Je vais donc tester ça !
01/03/2010, 14h46
Kropernic

Vraiment impressionnant.

Une dernière chose... Dans le cas d'un insert, je ne donne une valeur qu'au champ new_record et laisse old_record à null (et inversément dans le cas d'un delete).

J'ai un doute quant à la modification à apporter à la requête.

Prenons le cas de l'insert, il évident qu'il faut effacer la ligne commençant par cross apply où on fait usage de la table deleted. Par contre pour les jointures, j'avoue que je patauge complètement là dedans (je devrais réviser :oops:).

Je vais bien sûr tester de ce pas et tâcher d'en tirer mes propres conclusions mais j'apprécierais qu'un œil expert y jette un regard.

Merci beaucoup en tout cas !

Bon...

Voici ce que j'ai produit pour le cas d'une insertion :
Code:

1 2 3 4 5 6 7 INSERT INTO S_LOG.tb_Log (table_id, new_record) SELECT TBL_ID, INS.Val FROM S_LOG.T_TRACKED_TABLES_TBL CROSS JOIN INSERTED CROSS APPLY (SELECT cast((SELECT inserted.* for xml path('')) as varchar(max))) as INS(Val) WHERE TBL_SCHEMA = 'dbo' AND TBL_NAME = 'tb_switch';
J'ai testé en insérant une ligne directement dans la table, cela fonctionne.
J'ai également tester en faisant quelque chose du genre "insert into matable select ... from matable where ..." et cela fonctionne aussi. Je suis donc pleinement satisfait (à moins que je sois passé à côté de quelque chose).

Si vous pouviez me confirmer que ceci est bien exact, ce serait fort aimable.

Merci d'avance pour le check de cette requête et encore merci infiniment pour l'aide qui m'a déjà été apportée (je serais vraiment peu de chose sans ce merveilleux forum).

Griftou.

02/03/2010, 17h23
elsuket

Citation:

La différence est qu'il s'agit d'un trigger d'historisation de données. J'ai besoin de savoir ce qui a été changé pour chaque record. Pas de savoir qu'on a changé 10 records d'un seul coup. Je veux donc que on update 10 records d'un coup, il y ait 10 lignes qui soient insérées dans la table où j'historise les changements.

Pour ça j'ai une table avec la liste des tables de la db avec un id correspondant et dans ma table d'historisation, j'ai ces champs-ci :

Table_Id : Int
Old_Record : text
New_Record : text

Ce que je fait est donc mettre sous forme de xml (ce n'est peut-être pas la meilleure manière de faire mais je n'ai trouvé que ça) les records de la table deleted dans Old_Record et de inserted dans New_Record.

Il me semble évident de ce fait que j'ai besoin que chaque record soit traité séparément pour pouvoir faire du tracage sur l'un ou l'autre élément bien précis...

Non.
Vous pouvez faire la comparaison des valeurs directement dans le trigger sans faire de ligne à ligne, et à l'aide des tables virtuelles INSERTED et DELETED.
En ce qui concerne l'historisation des données, lisez l'article de SQLPro à ce sujet.
Si vous êtes sous SQL Server 2008, utilisez le Change Data Capture ou Change Tracking, moins coûteux et bien performants.

@++ ;)
02/03/2010, 18h20
Kropernic

Merci pour votre réponse.

Je suis malheureusement sous sql server 2005. Quant à l'article de Sqlpro, je m'en suis largement inspiré pour arriver au résultat final (ce que j'ai montré ici n'est qu'une petite partie du script). C'était un article fort intéressant mais étant novice, je dois avouer que je n'y ai pas compris grand chose. J'ai donc bricoler un peu à ma manière en prenant ce que je comprenais (presque).