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;
/ |
Partager