Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP TABLE Chantier;
- DROP TABLE Projet;
- DROP TABLE Client;
- DROP TABLE Architecte;
- DROP TABLE Artisan;
- CREATE TABLE Client (
- idClient int PRIMARY KEY,
- nom varchar(20),
- prenom varchar(20),
- adresse varchar(100)
- );
- CREATE TABLE Architecte (
- idArchitecte int PRIMARY KEY,
- nom varchar(20),
- prenom varchar(20),
- fonction varchar(20),
- adresse varchar(100),
- dateEmbauche varchar (10),
- commission int
- );
- CREATE TABLE Projet (
- idProjet int PRIMARY KEY,
- intitule varchar(50),
- montant int,
- etat varchar(20),
- idClient int,
- idArchitecte int,
- CONSTRAINT fk_idclient
- FOREIGN KEY (idclient)
- REFERENCES Client(idClient),
- CONSTRAINT fk_idarchi
- FOREIGN KEY (idArchitecte)
- REFERENCES Architecte(idArchitecte)
- );
- CREATE TABLE Artisan (
- idArtisan int PRIMARY KEY,
- nom varchar(20),
- adresse varchar(100),
- corpsMetiers varchar(30)
- );
- CREATE TABLE Chantier (
- idProjet int,
- idArtisan int,
- budget int,
- date_debut varchar(10),
- date_fin varchar(10),
- CONSTRAINT PK_Chantier PRIMARY KEY (idProjet,idArtisan),
- CONSTRAINT fk_idprojet
- FOREIGN KEY (idProjet)
- REFERENCES Projet(idProjet),
- CONSTRAINT fk_idartisan
- FOREIGN KEY (idArtisan)
- REFERENCES Artisan(idArtisan)
- );
- CREATE TRIGGER trigInsertProjet after insert on Projet for each row
- begin
- dbms_output.put_line('Un nouveau projet est créé');
- end;
- /
- CREATE TRIGGER trigBudgetProjet after insert on Chantier for each row
- declare
- oldMontant integer;
- newMontant integer;
- begin
- SELECT montant INTO oldMontant FROM Projet WHERE idProjet = :NEW.idProjet;
- newMontant := oldMontant + :NEW.budget;
- UPDATE Projet SET montant = newMontant WHERE idProjet = :NEW.idProjet;
- end;
- /
- CREATE TRIGGER trigCommissionArchi after update on Projet for each row
- begin
- IF :NEW.etat = 'terminé' THEN
- UPDATE Architecte SET commission = :NEW.montant * 0.1 WHERE idArchitecte = :NEW.idArchitecte;
- END IF;
- end;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement