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... |
Partager