Discussion :

[Noel Sybille]

Bonjour,

J'ai 3 tables Compteurs, Entête Factures et Lignes Factures; à chaque nouvelle facture, j'incrémente le compteur de n° de facture dans la table Compteurs.
Est-il possible de gérer le compteur de n° de facture via un trigger lors de chaque insertion d'un entête de facture ? (je compte récupérer le numéro généré via RETURNING into ...). Etant newbie en SQL , je ne sais pas comment procéder pour écrire/créer un trigger pour faire cela ... J'ai lu bien des exemples de trigger, mais rien qui se rapproche de mon cas ou bien qui pourrait me mettre sur une piste. Merci d'avance pour votre aide.
NB : Le N° de facture ne peut pas être géré via un champ auto-increment, mais au sein d'une table dédiée à cet effet.

[SergioMaster]

Bonjour,

Le N° de facture ne peut pas être géré via un champ auto-increment, mais au sein d'une table dédiée à cet effet.

Tout à fait d'accord, cependant utiliser un Trigger ne va pas empêcher les "trous" dans la facturation si une insertion est faite puis annulée il faut être conscient de ce fait !

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
CREATE TABLE ENTETE_FACTURE
(
  IDINTERNE BIGINT NOT NULL,
  ANNEE INTEGER,
  NUMERO INTEGER,
  CONSTRAINT PK_ENTETE_FACTURE PRIMARY KEY (IDINTERNE)
);
 
CREATE UNIQUE INDEX IDX_ENTETE_FACTURE ON ENTETE_FACTURE (ANNEE,NUMERO);
 
CREATE TABLE COMPTEUR_FACTURE
(
  ANNEE INTEGER NOT NULL,
  NUMERO INTEGER,
  CONSTRAINT PK_COMPTEUR_FACTURE PRIMARY KEY (ANNEE)
);

pour l'identification interne, voici ce que donnerai le trigger

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
CREATE GENERATOR GEN_ENTETE_FACTURE_ID;
 
SET TERM !! ;
CREATE TRIGGER ENTETE_FACTURE_BI FOR ENTETE_FACTURE
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE tmp DECIMAL(18,0);
BEGIN
  IF (NEW.IDINTERNE IS NULL) THEN
    NEW.IDINTERNE = GEN_ID(GEN_ENTETE_FACTURE_ID, 1);
  ELSE
  BEGIN
    tmp = GEN_ID(GEN_ENTETE_FACTURE_ID, 0);
    if (tmp < new.IDINTERNE) then
      tmp = GEN_ID(GEN_ENTETE_FACTURE_ID, new.IDINTERNE-tmp);
  END
-- ici 
END!!
SET TERM ; !!

il suffit de rajouter le code concernant le compteur
1° Méthode
on prend le numéro avant de modifier le compteur;

Code :

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

1
2
3
4
 
-- à insérer après le calcul de l'id 
SELECT COALESCE(NUMERO,0)+1 FROM COMPTEUR_FACTURE WHERE ANNEE=NEW.ANNEE INTO NEW.NUMERO;
UPDATE OR INSERT INTO  COMPTEUR_FACTURE (ANNEE,NUMERO) VALUES NEW.ANNEE,NEW.NUMERO MATCHING (ANNEE);

2°Méthode
on modifie le compteur AVANT

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
SET TERM ^ ;
ALTER TRIGGER ENTETE_FACTURE_BI ACTIVE
BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE tmp DECIMAL(18,0);
DECLARE VARIABLE R INTEGER;
BEGIN
  IF (NEW.IDINTERNE IS NULL) THEN
    NEW.IDINTERNE = GEN_ID(GEN_ENTETE_FACTURE_ID, 1);
  ELSE
  BEGIN
    tmp = GEN_ID(GEN_ENTETE_FACTURE_ID, 0);
    if (tmp < new.IDINTERNE) then
      tmp = GEN_ID(GEN_ENTETE_FACTURE_ID, new.IDINTERNE-tmp);
  END
 SELECT ANNEE FROM COMPTEUR_FACTURE WHERE ANNEE=NEW.ANNEE INTO :R; 
 IF (R IS NULL)
  THEN INSERT INTO COMPTEUR_FACTURE(ANNEE,NUMERO) VALUES (NEW.ANNEE,1) RETURNING NUMERO INTO NEW.NUMERO;
  ELSE UPDATE COMPTEUR_FACTURE SET NUMERO=NUMERO+1 WHERE ANNEE=NEW.ANNEE RETURNING NUMERO INTO NEW.NUMERO; 
END^
SET TERM ; ^

3°Méthode
là je doit avouer avoir galéré avec la syntaxe de MERGE que je n'arrive pas à faire fonctionner si l'année n'existe pas

Code :

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

1
2
3
4
5
MERGE INTO COMPTEUR_FACTURE C 
 USING (SELECT ANNEE, COALESCE(NUMERO,0) AS NUM FROM COMPTEUR_FACTURE) N ON (C.ANNEE=N.ANNEE)
 WHEN MATCHED THEN UPDATE SET NUMERO=N.NUM+1; 
-- la partie not matched ne fonctionne pas 
 WHEN NOT MATCHED THEN INSERT (ANNEE,NUMERO) VALUES (NEW.ANNEE,1);

de plus MERGE ne semble pas retourner de valeur ?
si quelqu'un peut jeter un coup d’œil

[Noel Sybille]

Mille mercis pour votre réponse !
J'avais bossé dessus ce matin et était arrivée à un "code" similaire, mais ma synthaxe est complètement erronée par méconnaissance du SQL...

Tout à fait d'accord, cependant utiliser un Trigger ne va pas empêcher les "trous" dans la facturation si une insertion est faite puis annulée il faut être conscient de ce fait !

Oui, si une facture doit être supprimée, l'utilisateur pourra, après vérification que le n° est disponible, forcer un numéro précis pour une nouvelle facture.
Dans ce cas, NEW.IDINTERNE ne sera pas Null.

Code :

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

CREATE GENERATOR GEN_ENTETE_FACTURE_ID;

Je ne comprends pas l'utilisation de ce "generator" puisque mon compteur est géré via une table (un record=une année).
Pour la gestion du compteur, je préfère la 2ème méthode (on modifie le compteur AVANT et on la récupère via RETURNING; je pense que c'est + safe en multi-utilisateurs :-)

Pour la 3° méthode avec le MERGE, là, je patauge complètement... on oublie !

[SergioMaster]

Je ne comprends pas l'utilisation de ce "generator" puisque mon compteur est géré via une table (un record=une année).

une simple précaution que mon expérience me fait mettre en place systématiquement pour toute table ayant un index primaire non numérique ou composé , pas forcément une obligation donc. Dans ce jeu d'essai cela permet aussi de montrer ce qui peut se passer avec un simple générateur