Ce titre peut paraître un peu barbare, mais la question se résume à la chose suivante : comment donner une numération relatives à plusieurs lignes insérées simultanément dans une table.

Considérons l’exemple suivant…
Soit une table de facture pour laquelle nous voudrions avoir une numérotation dont la rupture s’effectue sur l’année. Par exemple, partant de la table suivante :

-- ************************************************************************* -- 

-- table pour les tests de calcul de n° de facture incrémenté 

-- ************************************************************************* -- 

CREATE TABLE dbo.T_FACTURE_FCT 

(FCT_ID      INT NOT NULL IDENTITY PRIMARY KEY, 

 FCT_AN      SMALLINT NOT NULL DEFAULT YEAR(CURRENT_TIMESTAMP), 

 FCT_NUMERO  INT); 

GO

Comment y insérer les données suivantes :

INSERT INTO dbo.T_FACTURE_FCT (FCT_AN) 

VALUES (2007), (2007),  

       (2008), (2008), (2008);

Et obtenir en final :

FCT_ID      FCT_AN FCT_NUMERO 

----------- ------ ----------- 

1           2007   1 

2           2007   2 

3           2008   1 

4           2008   2 

5           2008   3

La difficulté réside dans le fait que :

• pour être performante, l’insertion doit se faire à l’aide d’une table de compteur comme l’exprime l’article que j’ai écrit à ce sujet : http://sqlpro.developpez.com/cours/clefs/
• l’appel de la nouvelle clef doit se faire dans un trigger
• les trigger SQL Server sont ensemblistes (un seul déclenchement, même si il y a insertion de plusieurs lignes…)
• SQL Server n’admet pas de type ARRAY ou MULTISET

La solution consiste à utiliser xml en lieu et place de types non atomiques somme les tableaux ou les ensembles.
Ceci est grandement facilité par le haut niveau d’intégration de xml dans SQL Server, notamment au niveau des possibilités de requêtage via XPath et XQuery. Voir a ce sujet l’article de Rudi Bruchez : http://rudi.developpez.com/sqlserver/tutoriel/xquery/.

Un petit exemple valant mieux qu’un long discours, voici comment partant d’une structure xml bien conçue on peut obtenir une table :

XXX — erreur de parseur — code irreproductible ! — XXX

Voici maintenant le code magique à utiliser pour ce faire…

1) tout d’abord, la table du compteur :

-- ************************************************************************* -- 

-- table du compteur 

-- ************************************************************************* -- 

CREATE TABLE dbo.T_COMPTEUR_CPT 

(CPT_AN      SMALLINT NOT NULL PRIMARY KEY, 

 CPT_VALEUR  INT      NOT NULL); 

GO

2) ensuite la procédure de calcul des nouvelles clefs (sous forme de plages)

-- ************************************************************************* -- 

-- procédure d'obtention des jetons de compteurs 

-- ************************************************************************* -- 

CREATE PROCEDURE dbo.P_GET_NEW_KEY_XML 

   @ASKKEYS XML,       -- clefs demandées sur différentes années 

   @RSPKEYS XML OUTPUT -- valeur des premières clefs des différentes années 

AS 

 

-- paramètres NULL => retour NULL 

IF @ASKKEYS IS NULL  

BEGIN 

   SET @RSPKEYS = NULL; 

   RETURN; 

END;    

 

-- on pilote une transaction au plus haut niveau d'isolation 

-- afin d'élimioner le risque des lignes fantômes 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 

BEGIN TRANSACTION; 

 

-- si au moins une des années demandé n'existe pas pour les lignes du compteurs 

 

IF EXISTS (SELECT T.x.value('(AN/text())[1]', 'int') AS AN 

           FROM @ASKKEYS.nodes('/COMPTEURS/FCT') AS T(x) 

           EXCEPT 

           SELECT CPT_AN 

           FROM   dbo.T_COMPTEUR_CPT) 

BEGIN                

-- alors ajouter ces années non présentes 

   INSERT INTO dbo.T_COMPTEUR_CPT (CPT_AN, CPT_VALEUR)  

   SELECT T.AN, NOMBRE 

   FROM   (SELECT T.x.value('(AN/text())[1]', 'int') AS AN 

           FROM   @ASKKEYS.nodes('/COMPTEURS/FCT') AS T(x) 

           EXCEPT 

           SELECT CPT_AN 

           FROM   dbo.T_COMPTEUR_CPT) AS T 

          INNER JOIN ((SELECT T.x.value('(AN/text())[1]', 'int') AS AN,  

                              1 +  T.x.value('(NOMBRE/text())[1]', 'int') AS NOMBRE  

                       FROM   @ASKKEYS.nodes('/COMPTEURS/FCT') AS T(x)) ) AS TX 

                ON T.AN = TX.AN; 

   IF @@ERROR <> 0 GOTO LBL_ERROR; 

END;    

ELSE 

BEGIN 

-- sinon, on ajoute le nombre de clefs à donner 

   UPDATE dbo.T_COMPTEUR_CPT 

   SET    CPT_VALEUR = CPT_VALEUR + TX.NOMBRE 

   FROM   dbo.T_COMPTEUR_CPT AS C 

          CROSS JOIN (SELECT T.x.value('(AN/text())[1]', 'int') AS AN,  

                             1 +  T.x.value('(NOMBRE/text())[1]', 'int') AS NOMBRE  

                      FROM   @ASKKEYS.nodes('/COMPTEURS/FCT') AS T(x)) AS TX 

   WHERE  CPT_AN = TX.AN;     

   IF @@ERROR <> 0 GOTO LBL_ERROR; 

END;    

-- à noter que ce code peut être transformé en MERGE 

 

-- récupération de la première clef de chaque année  

SET @RSPKEYS =  

(  

SELECT KEYS.AN, KEYS.NOMBRE, CPT_VALEUR - KEYS.NOMBRE AS FIRSTKEY 

FROM   dbo.T_COMPTEUR_CPT AS C 

       INNER JOIN (SELECT T.x.value('(AN/text())[1]', 'int') AS AN,  

                          T.x.value('(NOMBRE/text())[1]', 'int') AS NOMBRE  

                   FROM   @ASKKEYS.nodes('/COMPTEURS/FCT') AS T(x)) AS KEYS 

             ON C.CPT_AN = KEYS.AN 

FOR XML AUTO, ELEMENTS, ROOT ('COMPTEURS')); 

IF @@ERROR <> 0 GOTO LBL_ERROR; 

 

-- tout est parfait on valide 

COMMIT TRANSACTION; 

GOTO LBL_RESUME; 

 

-- an cas d'erreur on annule 

LBL_ERROR: 

ROLLBACK TRANSACTION; 

 

-- dans tous les cas on se replace au niveau d'isolation par défaut 

LBL_RESUME: 

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 

 

GO

3) et pour finir le trigger qui joue le jeu

-- ************************************************************************* -- 

-- déclencheur de calcul des clefs, gère l'insertion multi ligne 

-- ************************************************************************* -- 

CREATE TRIGGER dbo.E_I_FACTURE_XML 

ON dbo.T_FACTURE_FCT 

FOR INSERT 

AS 

 

DECLARE @ASKKEYS XML,@RSPKEYS XML; 

 

-- calcul préalable 

SET @ASKKEYS = (SELECT FCT.FCT_AN AS AN, COUNT(*) AS NOMBRE, 0 AS FIRSTKEY  

                FROM   dbo.T_FACTURE_FCT AS FCT 

                       INNER JOIN inserted AS I  

                             ON FCT.FCT_ID = I.FCT_ID 

                GROUP BY FCT.FCT_AN 

                FOR XML AUTO, ELEMENTS, ROOT ('COMPTEURS')) 

 

-- récupération des premières clefs en fonction du nombre de clefs demandées et des années 

EXEC dbo.P_GET_NEW_KEY_XML @ASKKEYS, @RSPKEYS OUTPUT; 

 

-- mise à jour de la table.  

-- Notez l'utilisation de la fonction de fenêtrage ROW_NUMBER 

UPDATE dbo.T_FACTURE_FCT 

SET    FCT_NUMERO = FIRSTKEY + N - 1 

FROM   dbo.T_FACTURE_FCT AS FCT 

       INNER JOIN (SELECT FCT_ID, ROW_NUMBER() OVER (PARTITION BY FCT_AN ORDER BY FCT_ID) AS N 

                   FROM   inserted) AS i 

             ON FCT.FCT_ID = i.FCT_ID                    

       INNER JOIN (SELECT T.x.value('(AN/text())[1]', 'int') AS AN,  

                          T.x.value('(FIRSTKEY/text())[1]', 'int') AS FIRSTKEY  

                   FROM   @RSPKEYS.nodes('/COMPTEURS/KEYS') AS T(x)) AS TX 

             ON FCT.FCT_AN = TX.AN;                    

 

GO

Il ne vous reste plus qu’à tester… puis comprendre !

INSERT INTO dbo.T_FACTURE_FCT (FCT_AN) 

VALUES (2007), (2007), (2008), (2008), (2008); 

 

SELECT *  

FROM   dbo.T_FACTURE_FCT  

ORDER  BY FCT_AN, FCT_NUMERO; 

 

SELECT *  

FROM dbo.T_COMPTEUR_CPT;

-------- 

Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 

Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 

www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 

* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *