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
| CREATE OR REPLACE TRIGGER MAJ_VENTE_CLIENT
BEFORE INSERT OR UPDATE ON VENTE_CLIENT
FOR EACH ROW
DECLARE
code VARCHAR2(6);
nom VARCHAR2(50);
montant NUMBER;
time VARCHAR2(4);
time2 VARCHAR2(4);
code2 VARCHAR2(6);
CURSOR C1 IS
SELECT ED.CODE_CLIENT, C.NOM_CLIENT, SUM(ED.MONTANT_DOC), to_char(ED.DATE_DOC,'yyyy') INTO code, nom,montant, time
FROM ENTETE_DOCUMENTS ED, CLIENTS C
WHERE ED.CODE_CLIENT = C.CODE_CLIENT
GROUP BY ED.CODE_CLIENT, C.NOM_CLIENT, to_char(ED.DATE_DOC,'yyyy')
;
CURSOR C2 IS
SELECT CODE_CLIENT, ANNEE INTO code2, time2
FROM VENTE_CLIENT
;
BEGIN
OPEN C1;
OPEN C2;
LOOP
FETCH C2 INTO code2, time2;
FETCH C1 INTO code, nom, montant, time;
EXIT WHEN C2%NOTFOUND;
EXIT WHEN C1%NOTFOUND;
if (code = code2 and time = time2) THEN
UPDATE VENTE_CLIENT
SET MONTANT = montant
;
ELSE
INSERT INTO VENTE_CLIENT (CODE_CLIENT, NOM_CLIENT, MONTANT, ANNEE) VALUES(code, nom, montant, time);
END if;
END LOOP;
CLOSE C2;
CLOSE C1;
END; |
Partager