· 8 years ago · Mar 13, 2018, 09:04 AM
1 drop table employe cascade constraints;
2 drop table projet cascade constraints;
3 drop table travail cascade constraints;
4 drop table service cascade constraints;
5 --drop trigger trigger_salaire;
6 --drop trigger trigger_hebdo;
7 --drop trigger trigger_delete_Emp;
8 --drop trigger trigger_delete_Proj;
9 --drop trigger trigger_sum_hebdo_Employe;
10 --drop trigger trigger_sum_hebdo_Travail;
11 --drop trigger trigger_nb_Proj;
12 --drop trigger trigger_salaire_error_Service ;
13
14 create table employe as select * from basetd.employe;
15 alter table employe add salaire NUMBER(4,0);
16 create table service as select * from basetd.service;
17 create table travail as select * from basetd.travail;
18 create table projet as select * from basetd.projet;
19
20
21 alter table employe add constraint PK_employe primary key (nuempl) ;
22 alter table service add constraint PK_service primary key (nuserv) ;
23 alter table projet add constraint PK_projet primary key (nuproj) ;
24 alter table travail add constraint PK_travail primary key (nuempl, nuproj) ;
25
26 alter table employe add constraint FK_employe foreign key (affect) references service(nuserv) ;
27 alter table employe add constraint U_employe unique(nuempl,affect) ;
28 alter table projet add constraint FK_projet foreign key (resp,nuproj) references travail(nuempl,nuproj) ;
29 alter table travail add constraint FK_travailE foreign key (nuempl) references employe(nuempl) initially deferred ;
30 /*alter table travail drop constraint FK_travailE;*/
31 alter table travail add constraint FK_travailP foreign key (nuproj) references projet(nuproj) initially deferred ;
32 /*alter table travail drop constraint FK_travailP;*/
33 alter table service add constraint FK_SERVICE foreign key (chef,nuserv) references employe(nuempl,affect) INITIALLY deferred;
34
35 alter table employe add constraint chk_hebdo check (hebdo <= 35);
36
37
38
39 /*On raccourcit le temps de travail des employés qui travaillent plus que keur temps hebdo*/
40 Select * from employe e
41 where (select sum(duree) from travail t where e.nuempl=t.nuempl)> hebdo;
42
43
44 Select * from travail t where t.nuempl = 23;
45 Update travail set duree = 10 where nuempl = 23 and nuproj = 237;
46
47 Select * from travail t where t.nuempl = 41;
48 Update travail set duree = 15 where nuempl = 41 and nuproj = 103;
49
50 Select * from travail t where t.nuempl = 42;
51 Update travail set duree = 20 where nuempl = 42 and nuproj = 135;
52
53 Select * from travail t where t.nuempl = 14;
54 Update travail set duree = 5 where nuempl = 14 and nuproj = 492;
55
56 Select * from travail t where t.nuempl = 68;
57 Update travail set duree = 20 where nuempl = 68 and nuproj = 370;
58
59 Select * from travail t where t.nuempl = 71;
60 Update travail set duree = 15 where nuempl = 71 and nuproj = 492;
61
62 insert into service values(12,'Service',4);
63 insert into service values(15,'Service2',13);
64 insert into service values(31,'Service12',6);
65 insert into employe values(1,'Michel',35,2,0);
66 insert into employe values(13,'Bob2',35,15,3000);
67 insert into employe values(4,'Bob',35,15,1000);
68 insert into employe values(1,'Jacques',36,15,0);
69
70 insert into projet values (3,'proj10',1);
71 insert into projet values (4,'proj2',2);
72 insert into travail values(2,3,60);
73 insert into travail values(2,3,51);
74
75
76
77 update employe set salaire = 2000;
78
79 update employe set salaire = 2500 where
80 nuempl in (select resp from PROJET);
81
82 update employe set salaire = 3500 where
83 nuempl in (select chef from service);
84
85
86
87 Create trigger trigger_salaire after update of salaire on employe
88 for each row
89 begin
90 if :new.salaire < :old.salaire then
91 raise_application_error (-20001,'viol de la regle: le salaire d employe ne peux pas etre dimiuné');
92 end if;
93 end;
94
95
96 Create trigger trigger_hebdo after update of hebdo on employe
97 for each row
98 begin
99 if :new.hebdo > :old.hebdo then
100 raise_application_error (-20001,'viol de la regle: la durée hebdo d un employe ne peux pas augmenter');
101 end if;
102 end;
103
104
105 Create trigger trigger_delete_Emp after delete on employe
106 begin
107 delete from TRAVAIL where nuempl not in (select employe.NUEMPL from employe);
108 end;
109
110
111
112
113 Create trigger trigger_delete_Proj after delete on projet
114 begin
115 delete from TRAVAIL where nuproj not in (select projet.nuproj from projet);
116 end;
117
118
119
120
121 create trigger trigger_sum_hebdo_Travail after update or insert on travail
122 declare
123 rec_employe employe%ROWTYPE;
124 begin
125 Select * into rec_employe from employe e
126 where (select sum(duree) from travail t where e.nuempl=t.nuempl)> hebdo;
127 raise_application_error(-20001,'Viol de regle: Un employe travaille plus que son temps hebdomadaire');
128
129 exception
130 when no_data_found then null;
131 when too_many_rows then
132 raise_application_error(-20001,'Viol de regle: Des employes travaille plus que leurs temps hebdomadaire');
133 end;
134
135
136 create trigger trigger_sum_hebdo_Employe after update on employe
137 declare
138 rec_employe employe%ROWTYPE;
139 begin
140 Select * into rec_employe from employe e
141 where (select sum(duree) from travail t where e.nuempl=t.nuempl)> hebdo;
142 raise_application_error(-20001,'Viol de regle: Un employe travaille plus que son temps hebdomadaire');
143
144 exception
145 when no_data_found then null;
146 when too_many_rows then
147 raise_application_error(-20001,'Viol de regle: Des employes travaille plus que leurs temps hebdomadaire');
148 end;
149
150
151 create trigger trigger_nb_Proj after update or insert on projet
152 declare
153 rec_employe employe%ROWTYPE;
154 begin
155 Select * into rec_employe from employe e
156 where (select count(*) from projet p where e.nuempl=p.resp)> 3 ;
157 raise_application_error(-20001,'Viol de règle: un employe est responsable au plus de 3 projets');
158 exception
159 when no_data_found then null;
160 when too_many_rows then
161 raise_application_error(-20001,'Viol de règle: un employe est responsable au plus de 3 projets');
162 end;
163
164
165 Create trigger trigger_salaire_error_Service after update or insert on employe
166 declare
167 rec_employe employe%rowtype;
168 begin
169 select * into rec_employe from employe e1
170 where e1.nuempl in (select chef from service) and
171 e1.salaire < (select max(salaire) from employe e2 where e1.affect = e2.affect and e1.nuempl != e2.nuempl);
172
173 raise_application_error(-20001,'Viol de règle: un responsable de service gagne plus que les employes du service');
174 exception
175 when no_data_found then null;
176 when too_many_rows then
177 raise_application_error(-20001,'Viol de règle: un responsable de service gagne plus que les employes du service');
178 end;
179
180-- insert into employe values(204,'marcel',34,1,1500);
181-- insert into travail values(204,79,1);
182-- insert into PROJET values (79,'testproj',204);
183
184 Create trigger trigger_salaire_error_Projet after update or insert on employe
185 declare
186 rec_employe employe%rowtype;
187 begin
188 select * into rec_employe from employe e1
189 where e1.nuempl in (select chef from service) and
190 e1.salaire < (select max(salaire) from employe e2 where e2.nuempl in (select resp from projet)
191 and e1.nuempl != e2.nuempl);
192 raise_application_error(-20001,'Viol de règle: un responsable de service gagne plus que les employes du service');
193 exception
194 when no_data_found then null;
195 when too_many_rows then
196 raise_application_error(-20001,'Viol de règle: un responsable de service gagne plus que les employes du service');
197 end;
198
199 select chef.nuempl,chef.NOMEMPL,chef.hebdo,chef.Affect,chef.salaire from service serv, employe chef
200 where serv.chef = chef.nuempl and exists (select * from projet proj, employe resp
201 where proj.resp = resp.nuempl
202 and resp.salaire > chef.salaire);
203
204 delete from projet where nuproj = 103;
205 delete from employe where nuempl = 23;
206 update EMPLOYE set salaire = 1900 where nuempl = 20;
207 update employe set hebdo = 25 where nuempl = 51;
208
209
210--
211-- CREATE OR REPLACE TRIGGER EMPLOYE_HAUT_SALAIRE
212-- AFTER INSERT OR DELETE OR UPDATE
213-- ON Employe
214-- FOR EACH ROW
215-- Begin
216-- If ((INSERTING and :NEW.salaire > 5000) or
217-- (UPDATING and :OLD.salaire <= 5000 and :NEW.salaire > 5000)) Then
218-- INSERT INTO EMPLOYE_ALERTE (nuempl, nomempl, hebdo, affect, salaire)
219-- VALUES (:NEW.nuempl, :NEW.nomempl, :NEW.hebdo, :NEW.affect, :NEW.salaire);
220--
221-- Elsif ((DELETING and :OLD.salaire > 5000) or
222-- (UPDATING and :OLD.salaire > 5000 and :NEW.salaire <= 5000)) Then
223-- DELETE FROM EMPLOYE_ALERTE WHERE nuempl = :OLD.nuempl;
224--
225-- Elsif (UPDATING and :NEW.salaire > 5000) Then
226-- UPDATE EMPLOYE_ALERTE SET salaire = :NEW.salaire WHERE nuempl = :NEW.salaire;
227--
228-- End If ;
229-- End ;