CREATE TABLE TACHE
(
id_tache INT NOT NULL
, TacheNom VARCHAR(32) NOT NULL
, PRIMARY KEY (id_tache)
) ;
CREATE TABLE CLIENT
(
id_client INT NOT NULL,
Clientnom VARCHAR(32) NOT NULL,
PRIMARY KEY (id_client)
) ;
CREATE TABLE PROJET
(
id_client INT NOT NULL
, id_projet INT NOT NULL
, id_tache INT NOT NULL
, Reference VARCHAR(64) NOT NULL
, LangueSource VARCHAR(64) NOT NULL
, PRIMARY KEY (id_client, id_projet)
, CONSTRAINT LIGNE_PROJET_TACHE_FK1 FOREIGN KEY (id_tache) REFERENCES TACHE (id_tache)
, CONSTRAINT PROJET_CLIENT_FK2 FOREIGN KEY (id_client) REFERENCES CLIENT (id_client)
) ;
CREATE TABLE FACTURE
(
id_client INT NOT NULL
, id_facture INT NOT NULL
, num_facture INT NOT NULL
, date_facture DATE NOT NULL
, PRIMARY KEY (id_client, id_facture)
, CONSTRAINT FACTURE_AK UNIQUE (num_facture)
, CONSTRAINT FACTURE_CLIENT_FK FOREIGN KEY (id_client)
REFERENCES CLIENT (id_client)
) ;
CREATE TABLE LIGNE_FACTURE
(
id_client INT NOT NULL
, id_projet INT NOT NULL
, id_facture INT NOT NULL
, PRIMARY KEY (id_client, id_projet)
, CONSTRAINT LIGNE_FACTURE_FACTURE_FK1 FOREIGN KEY (id_client, id_facture)
REFERENCES FACTURE (id_client, id_facture) ON DELETE CASCADE
, CONSTRAINT LIGNE_FACTURE_PROJET_FK1 FOREIGN KEY (id_client, id_projet)
REFERENCES PROJET (id_client, id_projet)
) ;
CREATE TABLE HEURE
(
id_client INT NOT NULL
, id_projet INT NOT NULL
, TatifHoraire INT NOT NULL
, PRIMARY KEY (id_client, id_projet)
, CONSTRAINT HEURE_LIGNE_PROJET_FK1 FOREIGN KEY (id_client, id_projet)
REFERENCES PROJET (id_client , id_projet) ON DELETE CASCADE
) ;
CREATE TABLE MOT
(
id_client INT NOT NULL
, id_projet INT NOT NULL
, id_mot INT NOT NULL
, NbMots INT NOT NULL
, Tarif INT NOT NULL
, MatchValeur VARCHAR(64) NOT NULL
, PRIMARY KEY (id_client, id_projet, id_mot)
, CONSTRAINT MOT_LIGNE_PROJET_FK1 FOREIGN KEY (id_client, id_projet)
REFERENCES PROJET (id_client, id_projet)
ON DELETE CASCADE
) ;
CREATE TABLE FORFAIT
(
id_client INT NOT NULL
, id_projet INT NOT NULL
, tarif_forfait INT NOT NULL
, PRIMARY KEY (id_client, id_projet)
, CONSTRAINT FORFAIT_LIGNE_PROJET_FK1 FOREIGN KEY (id_client, id_projet)
REFERENCES PROJET (id_client, id_projet) ON DELETE CASCADE
) ;
-- -------------------------------------------------------------------------------------------
-- Le délimiteur entre instructions CREATE n’est plus le « ; » mais « GO » :
-- -------------------------------------------------------------------------------------------
COMMIT ;
DELIMITER GO
CREATE TRIGGER PROJET_INCREMENT_TR BEFORE INSERT ON PROJET
FOR EACH ROW
BEGIN
SET NEW.id_projet = (SELECT COALESCE(MAX(id_projet) + 1, 1)
FROM PROJET
WHERE id_client = NEW.id_client) ;
END
GO
CREATE TRIGGER MOT_EXCLUSION_TR BEFORE INSERT ON MOT
FOR EACH ROW
BEGIN
SET @N = (
SELECT COUNT(*)
FROM FORFAIT
WHERE id_client = NEW.id_client
AND id_projet = NEW.id_projet
) ;
IF @N > 0 THEN
SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'Un mot ne peut pas être en même temps un forfait.' ;
END IF ;
SET @N = (
SELECT COUNT(*)
FROM HEURE
WHERE id_client = NEW.id_client
AND id_projet = NEW.id_projet
) ;
IF @N > 0 THEN
SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'Un mot ne peut pas être en même temps une heure.' ;
END IF ;
-- partie identification relative
SET NEW.id_mot = (SELECT COALESCE(MAX(id_mot) + 1, 1)
FROM MOT
WHERE id_client = NEW.id_client AND id_projet = NEW.id_projet) ;
END ;
GO
CREATE TRIGGER FORFAIT_EXCLUSION_TR BEFORE INSERT ON FORFAIT
FOR EACH ROW
BEGIN
SET @N = (
SELECT COUNT(*)
FROM HEURE
WHERE id_client = NEW.id_client
AND id_projet = NEW.id_projet
) ;
IF @N > 0 THEN
SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'Un forfait ne peut pas être en même temps une heure.' ;
END IF ;
SET @N = (
SELECT COUNT(*)
FROM MOT
WHERE id_client = NEW.id_client
AND id_projet = NEW.id_projet
) ;
IF @N > 0 THEN
SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'Un forfait ne peut pas être en même temps un mot.' ;
END IF ;
END ;
GO
-- ------------------------------------------------------------------------------------------
-- Le délimiteur « ; « retrouve son rôle normal
-- ------------------------------------------------------------------------------------------
DELIMITER ;
COMMIT ;
INSERT INTO CLIENT (id_client, Clientnom) VALUES (1, 'Ets Naudin') ;
INSERT INTO CLIENT (id_client, Clientnom) VALUES (2, 'Volfoni SA') ;
INSERT INTO CLIENT (id_client, Clientnom) VALUES (3, 'Dubicobit') ;
SELECT *, '' AS '<= CLIENT' FROM CLIENT ;
INSERT INTO TACHE (id_tache, TacheNom) VALUES (1, 'tâche 1') ;
INSERT INTO TACHE (id_tache, TacheNom) VALUES (2, 'tâche 2') ;
INSERT INTO TACHE (id_tache, TacheNom) VALUES (3, 'tâche 3') ;
INSERT INTO TACHE (id_tache, TacheNom) VALUES (4, 'tâche 4') ;
SELECT *, '' AS '<= TACHE' FROM TACHE ;
INSERT INTO PROJET (id_client, id_projet, id_tache, Reference, LangueSource) VALUES (1, 1, 1, 'réf 1 Naudin', 'provençal') ;
INSERT INTO PROJET (id_client, id_projet, id_tache, Reference, LangueSource) VALUES (1, 2, 1, 'réf 2 Naudin', 'chitimi') ;
INSERT INTO PROJET (id_client, id_projet, id_tache, Reference, LangueSource) VALUES (1, 3, 1, 'réf 2 Naudin', 'gallois') ;
INSERT INTO PROJET (id_client, id_projet, id_tache, Reference, LangueSource) VALUES (2, 1, 1, 'réf 1 de Volfoni', 'javanais') ;
INSERT INTO PROJET (id_client, id_projet, id_tache, Reference, LangueSource) VALUES (2, 2, 1, 'réf 2 de Volfoni', 'quebecois') ;
INSERT INTO PROJET (id_client, id_projet, id_tache, Reference, LangueSource) VALUES (2, 3, 1, 'réf 3 de Volfoni', 'breton') ;
INSERT INTO PROJET (id_client, id_projet, id_tache, Reference, LangueSource) VALUES (3, 1, 2, 'réf 1 de Dubicobit', 'basque') ;
SELECT *, '' AS '<= PROJET' FROM PROJET ;
-- --------------------------------------------------------------------------------------
INSERT INTO FACTURE (id_client, id_facture, num_facture, date_facture) VALUES (1, 1, 123456789, '2015_01_26') ;
INSERT INTO FACTURE (id_client, id_facture, num_facture, date_facture) VALUES (1, 2, 145320147, '2015_01_26') ;
INSERT INTO FACTURE (id_client, id_facture, num_facture, date_facture) VALUES (1, 3, 158500036, '2015_01_27') ;
INSERT INTO FACTURE (id_client, id_facture, num_facture, date_facture) VALUES (2, 1, 247820143, '2015_01_26') ;
INSERT INTO FACTURE (id_client, id_facture, num_facture, date_facture) VALUES (2, 2, 257896022, '2015_01_27') ;
INSERT INTO FACTURE (id_client, id_facture, num_facture, date_facture) VALUES (2, 3, 281478943, '2015_01_28') ;
INSERT INTO FACTURE (id_client, id_facture, num_facture, date_facture) VALUES (2, 4, 291475478, '2015_01_29') ;
INSERT INTO FACTURE (id_client, id_facture, num_facture, date_facture) VALUES (3, 1, 347820143, '2015_01_19') ;
SELECT *, '' AS '<= FACTURE' FROM FACTURE ;
INSERT INTO FORFAIT (id_client, id_projet, tarif_forfait) VALUES (1, 1,10000) ;
SELECT *, '' AS '<= FORFAIT' FROM FORFAIT ;
INSERT INTO MOT (id_client, id_projet, id_mot, NbMots, Tarif, MatchValeur) VALUES (1, 1, 0, 50, 100, 'doublonne avec FORFAIT !') ;
INSERT INTO MOT (id_client, id_projet, id_mot, NbMots, Tarif, MatchValeur) VALUES (1, 2, 0, 50, 100, 'Bonjour Madame') ;
INSERT INTO MOT (id_client, id_projet, id_mot, NbMots, Tarif, MatchValeur) VALUES (1, 2, 0, 50, 200, 'Bonsoir Monsieur') ;
INSERT INTO MOT (id_client, id_projet, id_mot, NbMots, Tarif, MatchValeur) VALUES (2, 1, 0, 10, 300, 'Sorry') ;
INSERT INTO MOT (id_client, id_projet, id_mot, NbMots, Tarif, MatchValeur) VALUES (2, 1, 0, 10, 400, 'Query') ;
INSERT INTO MOT (id_client, id_projet, id_mot, NbMots, Tarif, MatchValeur) VALUES (2, 1, 0, 10, 500, 'Language') ;
SELECT *, '' AS '<= MOT' FROM MOT ORDER BY 1, 2, 3 ;
-- -------------------------------------------------------------------------------------------
INSERT INTO FORFAIT (id_client, id_projet, tarif_forfait) VALUES (1, 2,10000) ;
SELECT *, '' AS '<= FORFAIT' FROM FORFAIT ;
Partager