create or replace TRIGGER CHEQUEVALIDE_ANNULE
BEFORE UPDATE OF ETATCHCODE ON CHEQUE
REFERENCING OLD AS A NEW AS N
FOR EACH ROW
DECLARE
v_opmontant number;
v_cptnum number;
BEGIN
  IF :a.etatchcode = 2 and  :n.etatchcode=1 then
  --le cheque est traité
    update operation set etatopcode=1 where opnum =:n.opnum ;
    elsif :a.etatchcode = 2 and  :n.etatchcode=4 then
  --le cheque est refusé
  update operation set etatopcode=3 where opnum = :n.opnum;
  select cptnum,opmontant into v_cptnum, v_opmontant from operation where opnum=:n.opnum ;
  update compte set cptbalance=cptbalance -v_opmontant where cptnum=v_cptnum;
  end if;
END;