Discussion :

[Garhein]

Bonjour,

Afin de conserver un historique des modifications sur certaines données, le système suivant a été mis en place :

• 1 trigger sur les UPDATE et DELETE sur les tables dont les données doivent être suivies
• 1 table d'historique enregistrant les données de la table deleted au format XML

Mon problème se situe au niveau de la transformation des données en XML. Un ordre SQL de type UPDATE/DELETE peut impacter plusieurs enregistrements en une fois et la transformation XML génère un seul document XML, alors que je souhaiterai obtenir 1 document par ligne impactée.

Ordre DDL de création de la table d'historique et des index

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE TABLE T_HISTO_XML
(
	IDENT INT IDENTITY NOT NULL,
	TB_NAME VARCHAR(40) NOT NULL,
	XML_CONTENT XML NOT NULL,
	PRIMARY KEY (IDENT)
);
 
CREATE PRIMARY XML INDEX NDX_HISTO_XML_CONTENT ON T_HISTO_XML(XML_CONTENT);
CREATE XML INDEX NDX_HISTO_XML_PATH ON T_HISTO_XML(XML_CONTENT) USING XML INDEX NDX_HISTO_XML_CONTENT FOR PATH;
CREATE XML INDEX NDX_HISTO_XML_PROPERTY ON T_HISTO_XML(XML_CONTENT) USING XML INDEX NDX_HISTO_XML_CONTENT FOR PROPERTY;
CREATE XML INDEX NDX_HISTO_XML_VALUE ON T_HISTO_XML(XML_CONTENT) USING XML INDEX NDX_HISTO_XML_CONTENT FOR VALUE;

Extrait du trigger

Code :

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

1
2
 
INSERT INTO T_HISTO_XML ( TB_NAME, XML_CONTENT ) VALUES ( 'TB_NAME', (SELECT * FROM deleted FOR XML PATH, ROOT('ROOT_NODE')))

Résultat actuel

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
17
18
19
 
<ROOT_NODE>
  <row>
    <CODE>BB41</INDI_CODE>
    <NOM>XXX</INDI_NOM>
    <PRNOM>XXX</INDI_PRNOM>
    <NOMJF>XXX</INDI_NOMJF>
    <INDI_DNAIS>2016-01-20</INDI_DNAIS>
    <IDENT>347820</IDENT>
  </row>
  <row>
    <CODE>BD30</INDI_CODE>
    <NOM>XXX</INDI_NOM>
    <PRNOM>XXX</INDI_PRNOM>
    <NOMJF>XXX</INDI_NOMJF>
    <INDI_DNAIS>2016-01-19</INDI_DNAIS>
    <IDENT>347821</IDENT>
  </row>
</ROOT_NODE>

Résultat escompté

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
17
18
19
20
21
22
 
<ROOT_NODE>
  <row>
    <CODE>BB41</INDI_CODE>
    <NOM>XXX</INDI_NOM>
    <PRNOM>XXX</INDI_PRNOM>
    <NOMJF>XXX</INDI_NOMJF>
    <INDI_DNAIS>2016-01-20</INDI_DNAIS>
    <IDENT>347820</IDENT>
  </row>
</ROOT_NODE>
 
<ROOT_NODE>
  <row>
    <CODE>BD30</INDI_CODE>
    <NOM>XXX</INDI_NOM>
    <PRNOM>XXX</INDI_PRNOM>
    <NOMJF>XXX</INDI_NOMJF>
    <INDI_DNAIS>2016-01-19</INDI_DNAIS>
    <IDENT>347821</IDENT>
  </row>
</ROOT_NODE>

Quelqu'un aurait-il une idée de la façon de procéder pour arriver à ce résultat ? Et est-il également possible de récupérer également dans le document XML les colonnes NULL ?

Merci d'avance.

[SQLpro]

1)

Code :

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

1
2
3
SELECT T.*, line.query('.')
FROM   T_HISTO_XML AS T
       CROSS APPLY XML_CONTENT.nodes('/ROOT_NODE/row') as TX(line)

2) utilisez l'option XSINIL dans la formation du XML
https://msdn.microsoft.com/en-us/library/ms190922.aspx

A +

[Garhein]

@SQLpro
1°)
La solution proposée permet de décomposer le document XML une fois que celui-ci est enregistré dans ma table d'historique. C'est à dire que si dans 1 enregistrement la colonne XML contient 3 noeud "row", la requête indiquée retourne 3 lignes de résultats.

Existe-il une méthode permettant d'enregistrer 3 lignes dans ma table d'historique (réalisée via mon trigger) si mon ordre UPDATE impacte 3 lignes différentes ?

2°)
Merci pour le lien, je vais me pencher dessus.

[SQLpro]

@SQLpro
1°)
La solution proposée permet de décomposer le document XML une fois que celui-ci est enregistré dans ma table d'historique. C'est à dire que si dans 1 enregistrement la colonne XML contient 3 noeud "row", la requête indiquée retourne 3 lignes de résultats.

Existe-il une méthode permettant d'enregistrer 3 lignes dans ma table d'historique (réalisée via mon trigger) si mon ordre UPDATE impacte 3 lignes différentes ?

La solution est la même appliquée au XML formé par la/les pseudo(s) table(s) INSERTED/DELETED dans la requête d'insertion dans la table d'historisation...
Cependant je vous déconseille cette approche car elle va considérablement augmenter le temps de traitement des mises à jours => verrous plus longtemps posés => contention => blocages => deadlocks....
La solution la plus simple, intelligente et élégante est d'utiliser la requête fournie pour "déplier" les lignes dans une vue !
D'autant plus qu'il est assez rare d'aller dans une table d'historisation alors que c'est la production qui prime !

2°)
Merci pour le lien, je vais me pencher dessus.

A +

[Garhein]

Merci pour toutes ces informations !

Je passe le sujet en "Résolu".