CREATE OR REPLACE TRIGGER PEDICLILIBERAR_IUD BEFORE INSERT OR UPDATE OF SITVEN ON PEDICLICAD FOR EACH ROW declare nCount integer; sNome char(30); begin nCount :=0; sNome :=' '; if :new.SITVEN = '1' then SELECT NOME into sNome FROM CLIENTE_R WHERE OID=:NEW.CODCLIE; SELECT count(*) into nCount FROM PEDICLILIBERAR where NUMPED = :NEW.NUMPED and FILIAL=:NEW.FILIAL; if nCount >0 then update PEDICLILIBERAR set DTPEDIDO= :new.DTPEDIDO, VALPED=:NEW.VALPED where NUMPED = :NEW.NUMPED and FILIAL=:NEW.FILIAL; else insert into PEDICLILIBERAR (FILIAL, NUMPED, DTPEDIDO, CODCLIE, NOME, VALPED,CODVEND) VALUES (:NEW.FILIAL, :NEW.NUMPED, :NEW.DTPEDIDO,:NEW.CODCLIE, sNOME, :NEW.VALPED, :NEW.CODVEND); end if; else if :new.SITVEN <> '1' and :old.SITVEN = '1' then SELECT count(*) into nCount FROM PEDICLILIBERAR where NUMPED = :NEW.NUMPED and FILIAL=:NEW.FILIAL; if nCount >0 then delete from PEDICLILIBERAR where NUMPED = :NEW.NUMPED and FILIAL=:NEW.FILIAL; end if; end if; end if; end; 
/