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
|
CREATE OR REPLACE TRIGGER ANOMALIES
AFTER INSERT ON CAPACITE FOR EACH ROW
declare
SimpleVar integer;
DoubleVar integer;
idResVar integer;
idCentreVar integer;
idClientVar integer;
NumSemaineVar integer;
AnneeVar integer;
NbrChSimpleVar integer;
NbrChDoubleVar integer;
DureeVar integer;
CURSOR c1 IS SELECT IdReservation,IdCentre.IdCentre,IdClient.IdClient,NumSemaine.NumSemaine,Annee.Anee,NbrChSimple,NbrChDouble,DureeSejour
FROM RESERVATION
WHERE IdCentre.IdCentre = :NEW.IdCentre
AND Annee.Annee = :NEW.Annee
AND NumSemaine.NumSemaine+DureeSejour > :NEW.NumSemaine
ORDER BY NumSemaine.NumSemaine;
BEGIN
SimpleVar := :NEW.NbrChSimpleDisponible;
DoubleVar := :NEW.NbrChDoubleDisponible;
OPEN C1;
FETCH C1 INTO idResVar,idCentreVar,idClientVar,AnneeVar,NumSemaineVar,NbrChSimpleVar,NbrChDoubleVar,DureeVar;
IF((SimpleVar-NbrChDoubleVar >=0) AND (DoubleVar-NbrChDoubleVar >=0))
THEN
SimpleVar := SimpleVar-NbrChSimpleVar;
DoubleVar := DoubleVar-NbrChDoubleVar;
END IF;
ELSE
BEGIN
INSERT INTO ANOMALIE Select seq_anomalie.nextval,idResVar,REF(a),REF(b),REF(c),REF(c),NbrChSimpleVar,NbrChDoubleVar,DureeVar
FROM CENTRE a, CLIENT b, SEMAINE c
Where a.IdCentre = idCentreVar
And b.IdClient = idClientVar
And c.Annee = AnneeVar
And c.NumSemaine = NumSemaineVar;
DELETE FROM RESERVATION WHERE IdReservation = idResVar;
END;
CLOSE C1;
END;
/ |
