Requête between dans un trigger

Version imprimable

Bonjour,

Je ne comprend pas pourquoi cette requête ne renvoi aucun résultat dans trigger:

Code:

1
2
 
SELECT FIRST 1 COALESCE(E.ROWID,0) FROM EMPLOYE_CONGE E WHERE E.EMPLOYEUR_ID=NEW.EMPLOYEUR_ID AND E.EMPLOYE_ID=NEW.EMPLOYE_ID AND  (NEW.CONGE_FROM BETWEEN E.CONGE_FROM AND E.CONGE_TO)

Et la même dans une ps ca marche:

Code:

1
2
3
 
SELECT FIRST 1 COALESCE(E.ROWID,0) FROM EMPLOYE_CONGE E WHERE E.EMPLOYEUR_ID=:EMPLOYEUR_ID AND E.EMPLOYE_ID=:EMPLOYE_ID
 AND  (:CONGE_FROM BETWEEN E.CONGE_FROM AND E.CONGE_TO) INTO :TOTAL;

Pourriez-vous SVP m'eclairer ?

Merci

Bonjour,

je pense que c'est parce que cela peut malgré tout ramené un NULL. Si aucun enregistrement n'est trouvé le COALESCE ne fonctionnera pas.
A mon avis il faut changer d'orientation, avec ce qui est donné je fais une hypothèse : il s'agit de trouver la nouvelle valeur de ROWID
Code:

1 2 3 4 5 6 7 8 ... DECLARE VARIBLE RESULT INTEGER; BEGIN SELECT COUNT(1) FROM EMPLOYE_CONGE E WHERE E.EMPLOYEUR_ID=NEW.EMPLOYEUR_ID AND E.EMPLOYE_ID=NEW.EMPLOYE_ID AND (NEW.CONGE_FROM BETWEEN E.CONGE_FROM AND E.CONGE_TO) INTO RESULT; IF (RESULT IS NULL) THEN NEW.ROWID=1; ELSE SELECT MAX(E.ROWID)+1 FROM EMPLOYE_CONGE E WHERE E.EMPLOYEUR_ID=NEW.EMPLOYEUR_ID AND E.EMPLOYE_ID=NEW.EMPLOYE_ID AND (NEW.CONGE_FROM BETWEEN E.CONGE_FROM AND E.CONGE_TO) INTO NEW_ROWID; ...
P.S. j'ai un doute : ROWID ne serait pas un nouveeau mot réservé (Firebird 3 permettant le fenêtrage ...)

12/11/2017, 15h36
freud
Effectivement le COALESCE ne marche pas dans le trigger mais j'ai dû maintenir le FIRST 1 au lieu du COUNT(1)

Pardon, je n'avais pas poster la totalité du code en fait ce n'est pas ROWID que j'incrémente mais juste une recherche de lignes avant d'incrémenter ou de lancer une exception dans un trigger before insert avec ce code:
Code:

1 2 if (new.rowid is null) then new.rowid = gen_id(gen_employe_conge,1);
Citation:

Envoyé par SergioMaster

P.S. j'ai un doute : ROWID ne serait pas un nouveau mot réservé (Firebird 3 permettant le fenêtrage ...)]

Non, sinon il me l'aurait refusé (je suis avec FB3) j'ai de nombreuses tables avec pour champ ROWID comme PK et n'ai pas croiser ce mot dans la partie nouveaux mots réservé de la doc FB3.

Merci SergioMaster et bonne journée. :)

Salut freud.

Pouvez-vous me confirmer que vous êtes bien sous FireBird 3 ?

Vous dites que vous avez mis cette requête dans un déclencheur (trigger), mais vous n'avez pas précisé si ce déclencheur est celui de la même table (EMPLOYE_CONGE) ou d'une autre table ?

Voici votre requête :

Code:

1
2
3
4
5
SELECT  FIRST 1 COALESCE(E.ROWID,0)
  FROM  EMPLOYE_CONGE E
 WHERE  E.EMPLOYEUR_ID = NEW.EMPLOYEUR_ID
   AND  E.EMPLOYE_ID   = NEW.EMPLOYE_ID
   AND  NEW.CONGE_FROM BETWEEN E.CONGE_FROM AND E.CONGE_TO

Je constate un léger problème d'écriture avec la clause "between".
Normalement, on met des dates sous forme de valeur et non des colonnes. Vous devez plutôt écrire :

Code:

1
2
    NEW.CONGE_FROM >= E.CONGE_FROM
AND NEW.CONGE_FROM <= E.CONGE_TO

En bon français, que cherchez-vous à faire ?
D'après ce que j'ai pu comprendre, vous désirez incrémenter la colonne "rowid".
Et cette même colonne "rowid", vous sert comme "primary key".

Je ne sais pas si vous le savez, mais une nouvelle fonctionnalité de FireBird 3, est de pouvoir incrémenter la primary key d'une manière automatique.

Code:

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
CREATE DATABASE '..\Data\Base.fdb' page_size 4096 DEFAULT CHARACTER SET WIN1252;
 
-- ====================================
-- Création de la table 'employe_conge'
-- ====================================
 
CREATE TABLE employe_conge (
  rowid         integer generated by default as identity not null primary key,
  employeur_id  integer not null,
  employe_id    integer not null
 
);
 
-- ==============================
-- Insertion dans 'employe_conge'
-- ==============================
 
insert into employe_conge (employeur_id,employe_id) values (25,12);
insert into employe_conge (employeur_id,employe_id) values (25,33);
insert into employe_conge (employeur_id,employe_id) values (75,14);
insert into employe_conge (employeur_id,employe_id) values (75,45);
insert into employe_conge (employeur_id,employe_id) values (25,41);
 
-- =========================
-- Vidage de 'employe_conge'
-- =========================
 
select * from employe_conge;
 
       ROWID EMPLOYEUR_ID   EMPLOYE_ID
============ ============ ============
           1           25           12
           2           25           33
           3           75           14
           4           75           45
           5           25           41
 
exit;
 
Appuyez sur une touche pour continuer...

13/11/2017, 14h24
SergioMaster

Bonjour

Citation:

Envoyé par Artemus24

Je constate un léger problème d'écriture avec la clause "between".
Normalement, on met des dates sous forme de valeur et non des colonnes.

au contraire le between est parfaitement acceptable

Citation:

Envoyé par freud

mais j'ai dû maintenir le FIRST 1 au lieu du COUNT(1)

Là par contre cela m'étonne

Bonjour Artemus24 et SergioMaster,

Oui je suis sous FB3.

Citation:

Envoyé par Artemus24

Je ne sais pas si vous le savez, mais une nouvelle fonctionnalité de FireBird 3, est de pouvoir incrémenter la primary key d'une manière automatique.

Dommage ça m' a échapper j'ai pourtant parcouru la doc.
Bon, ca sera pour le restant des tables. Merci !

Oui le déclencheur c'est bien celui de EMPLOYE_CONGE avant je ne sais pas ce qui s'est passé mais maintenant le COALESCE fonctionne et je n'ai pas de problème avec le BETWEEN.

Merci Artemus24 pour la requête mais j'ai dû lui rajouter un UNION avec la même requête et cette fois-ci pour tester le CONGE_TO bon je pensais en avoir terminer avec mais voilà que j'ai une fuite (un autre cas que je n'ai pas tester).

En fait, ce que je cherche à faire c'est lorsque j'insère un conge pour un employé, le début et la fin de période ne doivent pas se trouver dans d'autres
intervalles. C'est juste un contrôle pour éviter des erreurs de saisies. Que ce soit pour le début sans que la fin ne se trouve dans ces intervalles ou pour la fin sans que le début ne se trouve dans ces intervalles. Sauf que je n'ai tenu compte du cas où le début se trouve avant ces intervalles et la fin après ces intervalles. Exemple:

ROW CONGE_FROM CONGE_TO
----- ------------- -- ------------
1 01/11/2017 13/11/2017

Avec la requête ci-dessous

J'insère un congé du :

-05/11/2017 au 14/11/2017 : OK ca ne passe pas à cause du début qui se trouve dans l'intervalle de ROWID=1
-10/10/2017 au 10/11/2017 : OK ca ne passe pas à cause de la fin qui se trouve dans l'intervalle de ROWID=1
-10/10/2017 au 14/11/2017 : ca passe mais ne devrait pas passer là est la fuite.

Bon ca va se régler sinon je me rabas vers un CTE

Voici la requête:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 AS DECLARE VARIABLE RES INTEGER; DECLARE VARIABLE MES1 VARCHAR(200); BEGIN SELECT FIRST 1 COALESCE(ROWID,0) FROM EMPLOYE_CONGE WHERE EMPLOYEUR_ID=NEW.EMPLOYEUR_ID AND EMPLOYE_ID=NEW.EMPLOYE_ID AND (NEW.CONGE_FROM BETWEEN CONGE_FROM AND CONGE_TO) UNION SELECT FIRST 1 COALESCE(ROWID,0) FROM EMPLOYE_CONGE WHERE EMPLOYEUR_ID=NEW.EMPLOYEUR_ID AND EMPLOYE_ID=NEW.EMPLOYE_ID AND (NEW.CONGE_TO BETWEEN CONGE_FROM AND CONGE_TO) INTO :RES; RES=COALESCE(:RES,0); IF (:RES<>0) THEN BEGIN EXCEPTION...................................... END IF (NEW.ROWID IS NULL) THEN NEW.ROWID = GEN_ID(GEN_EMPLOYE_CONGE,1); END
Citation:

Envoyé par SergioMaster

Là par contre cela m'étonne

Non, je n'ai pas dis que le COUNT n'a pas marcher. Comme j'ai dû compléter ma requête en ajoutant une instruction UNION ca m'a renvoyer "un multiple rows in singleton select".

Re,

L' UNION peut, peut-être, se remplacer par , à tester
Code:

1 2 SELECT COUNT(1) FROM EMPLOYE_CONGE WHERE EMPLOYEUR_ID=NEW.EMPLOYEUR_ID AND EMPLOYE_ID=NEW.EMPLOYE_ID AND ((NEW.CONGE_FROM BETWEEN CONGE_FROM AND CONGE_TO) OR (NEW.CONGE_TO BETWEEN CONGE_FROM AND CONGE_TO)) INTO :RES;
AMHA, dans ce cas une colonne identity n'aurait pas été une bonne idée, par contre au temps pour ma proposition concernant l'incrément puisque, si je comprend bien c'est quand le résultat de cette SQL est null que l'on accepte (et incrémente le générateur) la ligne

un if exists pourrait peut être également testé
Code:

1 2 3 4 5 6 IF EXISTS(SELECT 1 FROM EMPLOYE_CONGE WHERE EMPLOYEUR_ID=NEW.EMPLOYEUR_ID AND EMPLOYE_ID=NEW.EMPLOYE_ID AND ((NEW.CONGE_FROM BETWEEN CONGE_FROM AND CONGE_TO) OR (NEW.CONGE_TO BETWEEN CONGE_FROM AND CONGE_TO))) THEN BEGIN IF (NEW.ROWID IS NULL) THEN NEW.ROWID = GEN_ID(GEN_EMPLOYE_CONGE,1); END ELSE EXCEPTION

Salut à tous.

Ce n'est qu'une simple remarque, et non une erreur en soit, j'ai pris l'habitude d'utiliser le between sous ce format là :

Code:

AND periode between '2017-11-01' and '2017-11-30'

c'est-à-dire avec des valeurs. Tandis qu'avec des colonnes, j'écris plutôt :

Code:

1
2
AND periode >= date_deb
AND periode <= date_fin

Il est fort probable que sous FireBird, on peut remplacer les valeurs par des colonnes.
Vu que j'ai plus l'habitude de MySql, cela me paraissait un peu bizarre sans être une erreur.

Autre remarque, mon exemple a été testé sous FireBird 3.0.2. Ne fonctionne pas sous FireBird 2.5.

J'ai repris l'idée de chevauchement de vos différentes périodes. Voici ce que je propose :

Code:

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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
CREATE DATABASE '..\Data\Base.fdb' page_size 4096 DEFAULT CHARACTER SET WIN1252;
 
-- =========
-- Exception
-- =========
 
create exception verif '''periode'' déjà existante ou se chevauchant !';
 
-- ====================================
-- Création de la table 'employe_conge'
-- ====================================
 
CREATE TABLE employe_conge
( rowid         integer generated by default as identity not null primary key,
  employeur_id  integer not null,
  employe_id    integer not null,
  conge_from    date    not null,
  conge_to      date    not null
);
 
-- ===========
-- Déclencheur
-- ===========
 
SET TERM #;
 
CREATE TRIGGER test
FOR employe_conge ACTIVE
BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE flag integer default null;
BEGIN
  SELECT  1
    FROM  EMPLOYE_CONGE
   WHERE  EMPLOYEUR_ID    = NEW.EMPLOYEUR_ID
     AND  EMPLOYE_ID      = NEW.EMPLOYE_ID
     AND  NEW.CONGE_FROM <= CONGE_TO
     AND  NEW.CONGE_TO   >= CONGE_FROM
    INTO  flag;
 
  IF (flag is not null) THEN exception verif;
END#
 
SET TERM ;#
 
-- ==============================
-- Insertion dans 'employe_conge'
-- ==============================
 
insert into employe_conge (employeur_id,employe_id,conge_from,conge_to) values (25,12,'2017-11-01','2017-11-15');
 
insert into employe_conge (employeur_id,employe_id,conge_from,conge_to) values (25,12,'2017-10-25','2017-11-07');
Statement failed, SQLSTATE = HY000
exception 1
-VERIF
-'periode' déjà existante ou se chevauchant !
-At trigger 'TEST' line: 15, col: 30
After line 50 in file Base.sql
insert into employe_conge (employeur_id,employe_id,conge_from,conge_to) values (25,12,'2017-11-07','2017-11-25');
Statement failed, SQLSTATE = HY000
exception 1
-VERIF
-'periode' déjà existante ou se chevauchant !
-At trigger 'TEST' line: 15, col: 30
After line 52 in file Base.sql
insert into employe_conge (employeur_id,employe_id,conge_from,conge_to) values (25,12,'2017-11-05','2017-11-10');
Statement failed, SQLSTATE = HY000
exception 1
-VERIF
-'periode' déjà existante ou se chevauchant !
-At trigger 'TEST' line: 15, col: 30
After line 53 in file Base.sql
insert into employe_conge (employeur_id,employe_id,conge_from,conge_to) values (25,12,'2017-10-15','2017-12-10');
Statement failed, SQLSTATE = HY000
exception 1
-VERIF
-'periode' déjà existante ou se chevauchant !
-At trigger 'TEST' line: 15, col: 30
After line 54 in file Base.sql
 
insert into employe_conge (employeur_id,employe_id,conge_from,conge_to) values (25,12,'2017-10-15','2017-10-31');
insert into employe_conge (employeur_id,employe_id,conge_from,conge_to) values (25,12,'2017-11-16','2017-11-30');
 
insert into employe_conge (employeur_id,employe_id,conge_from,conge_to) values (33,17,'2017-11-01','2017-11-15');
 
-- =========================
-- Vidage de 'employe_conge'
-- =========================
 
select * from employe_conge;
 
       ROWID EMPLOYEUR_ID   EMPLOYE_ID  CONGE_FROM    CONGE_TO
============ ============ ============ =========== ===========
           1           25           12 2017-11-01  2017-11-15
           6           25           12 2017-10-15  2017-10-31
           7           25           12 2017-11-16  2017-11-30
           8           33           17 2017-11-01  2017-11-15
 
exit;
 
Appuyez sur une touche pour continuer...

J'ai créé une exception, histoire de visualiser le rejet de la nouvelle ligne qui va s'insérer.
Les lignes de 2 à 5 sont des chevauchements de la ligne 1 et ont donc été rejetés.
6 et 7 sont correctes !

La ligne 8 est pour un autre employeur et un autre employé.

C'est une ébauche de votre problématique. A tester dans un contexte autre que celui de mon mini exemple.

@+

13/11/2017, 21h29
freud

Bonsoir,

Les deux requêtes de SergioMaster fonctionnent très bien et merci à vous SergioMaster. Alors, j'allais remplacer la mienne qui semble bourin avec son UNION (trop lourd quand même, hein ?) par celle de SergioMaster, tout en continuant à la travailler pour le 3eme cas de fuite (celui que j'ai mis en rouge dont la date ne devrait pas passer). Voilà que je teste la votre Artemus24 et, à ma grande surprise, elle me règle ce 3eme cas. Bah alors ce n'est pas seulement une ébauche mais aussi une finalité (????). Merci à vous Artemus24 et à Sergiomaster :ccool:
13/11/2017, 21h48
freud

Je me rend compte que je n'ai pas répondu à toutes les questions du faite de mon imprécision....

Citation:

Envoyé par SergioMaster

.....par contre au temps pour ma proposition concernant l'incrément puisque, si je comprend bien c'est quand le résultat de cette SQL est null que l'on accepte (et incrémente le générateur) la ligne

Oui effectivement, c'est quand le résultat est null que je laisse passer l'insertion.

Bonne fin de soirée à tous.