Discussion :

[bilou972]

Bonjour,
pour une facture, je dois donner un numéro de la forme FYY524DDDD
F = facture ou A=avoir
YY = année
524 = numéro de service
DDDD = numéro d'ordre séquentiel qui recommence tous les ans a 0000

Je pense donc faire un trigger qui donnera ce numéro automatiquement à l'insertion de la facture mais je ne vois pas comment faire pour recommencer la séquence chaque année, ni ou et comment stocker le numéro attribué, à moins de récupérer la sous-chaine a partir du 6ème caractère à chaque insertion

J'attends donc vos idées. Merci d'avance !

[mikedavem]

Bonjour,

Pour cette partie du compteur :

Code :

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

1
2
 
DDDD = numéro d'ordre séquentiel qui recommence tous les ans a 0000

Une table qui tient à jour votre numéro de compteur et la date de la dernière mise à jour de ce compteur. Par exemple :

Code :

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

1
2
3
4
5
6
7
 
CREATE TABLE t_compteur
(
   compteur_name VARCHAR(10) NOT NULL,
   compteur_no INT NOT NULL,
   last_date_maj DATETIME NOT NULL
)

Dans votre trigger vous n'avez plus qu'à récupérer cette date de mise à jour et comparer son année avec celle de votre nouvelle insertion.

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
 
IF YEAR(last_date_compteur) <> YEAR(GETDATE()) 
BEGIN
 
-- Je recommence la numérotation
-- Je mets à jour la table des compteurs avec le nouveau numéro 
-- et la nouvelle date de mise à jour
UPDATE t_compteur
SET compteur_no = 0,
      last_date_maj = GETDATE()
WHERE compteur_name = 'facture'
 
-- Je mets en forme mon nouveau numéro de facture
 
END

++

[SQLpro]

Saf que ceci n'est pas aussi simple que cela car les triggers étant par nature ensemblistes dans SQL Server, il vous faut implémenter un curseur, ou mieux une fonction ROW_NUMBER() et modifier la procédure pour permettre de numéroter d'un seul coup toutes les lignes.

A +

[mikedavem]

Exact !! Merci pour la remarque SqlPro.

J'ai répondu un peu vite.

Il faut en effet prendre en compte le fait que le déclenchent du trigger puisse concerner un ensemble de lignes (INSERTED). Du coup le code est un peu à revoir...

[SQLpro]

Voici en démo, la solution à votre problème :

Code :

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

1
2
3
4
5
6
7
-- ************************************************************************* --
-- 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_NUMERO  CHAR(13));
GO

Code :

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

1
2
3
4
5
6
7
-- ************************************************************************* --
-- table du compteur
-- ************************************************************************* --
CREATE TABLE dbo.T_COMPTEUR_CPT
(CPT_AN      SMALLINT NOT NULL PRIMARY KEY,
 CPT_VALEUR  INT      NOT NULL);
GO

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
-- ************************************************************************* --
-- procédure d'obtention d'un jeton de compteur
-- ************************************************************************* --
CREATE PROCEDURE dbo.P_GET_NEW_KEY @AN SMALLINT,      -- année considérée
                                   @NOMBRE INT,       -- nombre de clefs demandées
                                   @NEWKEY INT OUTPUT -- valeur de la première clef
AS
 
-- paramètres NULL => retour NULL
IF @AN IS NULL OR @NOMBRE IS NULL 
BEGIN
   SET @NEWKEY = 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 l'année n'existe pas on l'insère avec compteur à 1
IF NOT EXISTS (SELECT *
               FROM   dbo.T_COMPTEUR_CPT
               WHERE  CPT_AN = @AN)
BEGIN               
   INSERT INTO dbo.T_COMPTEUR_CPT (CPT_AN, CPT_VALEUR) 
   VALUES (@AN, 1 + @NOMBRE) 
   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 + @NOMBRE
   WHERE  CPT_AN = @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   
SELECT @NEWKEY = CPT_VALEUR - @NOMBRE
FROM   dbo.T_COMPTEUR_CPT
WHERE  CPT_AN = @AN;   
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

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
23
24
25
26
27
28
-- ************************************************************************* --
-- déclencheur de calcul des clefs, gère l'insertion multi ligne
-- ************************************************************************* --
CREATE TRIGGER dbo.E_I_FACTURE
ON dbo.T_FACTURE_FCT
FOR INSERT
AS
 
-- calcul préalable
DECLARE @AN SMALLINT, @NOMBRE INT, @NEWKEY INT;
SELECT @NOMBRE = COUNT(*), @AN = YEAR(CURRENT_TIMESTAMP)
FROM   inserted;
 
-- récupération de la première clef en fonction du nombre de clefs demandées
EXEC dbo.P_GET_NEW_KEY @AN, @NOMBRE, @NEWKEY 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 = RTRIM(FCT_NUMERO) 
                  + REPLICATE('0', 5 - LEN(CAST(@NEWKEY + N AS VARCHAR(16))))
                  + CAST(@NEWKEY + N - 1 AS VARCHAR(16))
FROM   dbo.T_FACTURE_FCT AS FCT
       INNER JOIN (SELECT FCT_ID, ROW_NUMBER() OVER (ORDER BY FCT_ID) AS N
                   FROM   inserted) AS i
             ON FCT.FCT_ID = i.FCT_ID;
 
GO

Code :

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

1
2
3
4
5
6
-- ************************************************************************* --
-- tests
-- ************************************************************************* --
 
INSERT INTO dbo.T_FACTURE_FCT
VALUES ('FYY52408'), ('FYY52408'), ('FYY52408');

Code :

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

1
2
3
4
5
6
7
SELECT * FROM  dbo.T_FACTURE_FCT;
 
FCT_ID      FCT_NUMERO
----------- -------------
1           FYY5240800001
2           FYY5240800002
3           FYY5240800003

Code :

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

1
2
3
4
5
SELECT * FROM dbo.T_COMPTEUR_CPT;
 
CPT_AN CPT_VALEUR
------ -----------
2008   4

CQFD et prochainement dans mon blog dans une version plus simple !

A +

[bilou972]

Wow, ça c'est du code sql !
Je ne pensais pas que ce serait si compliqué. La il est un peu tard mais je lirai demain en tout cas merci beaucoup à vous deux.
Tiens nous au courant pour la version de ton blog.

[SQLpro]

Version blog publiée ici : http://blog.developpez.com/sqlpro?ti...gnes_a_increma

il y a cependant un petit bug qui m'empêche de publier intégralement la chose...
A la place de :
XXX -- erreur de parseur -- code irreproductible ! -- XXX
Devrait figurer le texte suivant :

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
23
24
DECLARE @XML XML;
 
SET @XML = '<COMPTEURS>
             <FCT>
              <AN>2007</AN>
              <NOMBRE>2</NOMBRE>
              <FIRSTKEY>0</FIRSTKEY>
             </FCT>
             <FCT>
              <AN>2008</AN>
              <NOMBRE>3</NOMBRE>
              <FIRSTKEY>0</FIRSTKEY>
             </FCT>
            </COMPTEURS>';
 
SELECT T.x.value('(AN/text())[1]', 'int') AS AN,
       T.x.value('(NOMBRE/text())[1]', 'int') AS NOMBRE,
       T.x.value('(FIRSTKEY/text())[1]', 'int') AS FIRSTKEY
FROM   @XML.nodes('/COMPTEURS/FCT') AS T(x)
 
AN          NOMBRE      FIRSTKEY
----------- ----------- -----------
2007        2           0
2008        3           0

L'inverse étant on ne peut plus simple du fait de la présence de la clause FOR XML que l'on peut ajouter à toute requête de type SELECT :

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
SELECT FCT_AN AS AN, COUNT(*) AS NOMBRE, 0 AS FIRSTKEY 
FROM   dbo.T_FACTURE_FCT AS FCT
GROUP  BY FCT_AN
FOR XML AUTO, ELEMENTS, ROOT ('COMPTEURS')
 
XML_F52E2B61-18A1-11d1-B105-00805F49916B
-----------------------------------------
<COMPTEURS>
 <FCT>
  <AN>2007</AN>
  <NOMBRE>2</NOMBRE>
  <FIRSTKEY>0</FIRSTKEY>
 </FCT>
 <FCT>
  <AN>2008</AN>
  <NOMBRE>3</NOMBRE>
  <FIRSTKEY>0</FIRSTKEY>
 </FCT>
</COMPTEURS>

A +

[bilou972]

Merci beaucoup pour ton aide. Je n'ai pas eu l'occasion de tester la solution "XML".
Par contre j'ai adapté et mis en place la solution que tu avais donnée précédemment et elle fonctionne sans problème alors encore une fois merci.