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
| CREATE OR REPLACE TRIGGER TrgEnchereAuto
AFTER INSERT ON Enchere
DECLARE
Modif INTEGER;
EMax INTEGER;
CURSOR C IS Select * from Enchere;
BEGIN
LOOP
Modif:=0;
FOR i IN C
LOOP
IF i.NumIDAnnonce = :NEW.NumIDAnnonce
AND i.EnchereAuto=1
AND i.MontantMax>ALL(Select E.MontantMax
From Enchere E
Where E.NumIDAnnonce = :NEW.NumIDAnnonce
AND E.EnchereAuto=0)
AND i.MontantMax<ANY(Select E.MontantMax
From Enchere E
Where E.NumIDAnnonce = :NEW.NumIDAnnonce
AND E.NumID <>i.NumID)
THEN
Select MontantMax
INTO EMAX
From Enchere E
Where MontantMax>ALL(Select MontantMax
From Enchere e
Where e.NumIDAnnonce= :NEW.NumIDAnnonce)
AND E.NumIDAnnonce = :NEW.NumIDAnnonce;
INSERT INTO Enchere VALUES (i.NumID, i.Annonce, EMAX, sysdate, 0);
Modif := Modif + 1;
END IF;
END LOOP;
EXIT WHEN Modif=0;
END LOOP;
END;
/ --[Rajout aprés edition du message suite à la réponse de Michel SALAIS] |
Partager