Discussion :

[Kropernic]

Hello,

Dans un trigger d'update, je fais ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[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.

[Sergejack]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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)

[elsuket]

Bonjour,

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 )

@++

[Kropernic]

Ne pas utiliser de curseur.

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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" ???

[Kropernic]

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...

[Kropernic]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ^^

[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

[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.

[Sergejack]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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';

[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 !

[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 ).

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 !

[Kropernic]

Bon...

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[elsuket]

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.

@++

[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).