CREATE OR REPLACE TRIGGER TRG_UPD_FOTOPRATOFIL
AFTER INSERT OR DELETE OR UPDATE
ON FOTOPRAFIL FOR EACH ROW
DECLARE
V_EXIST_FOTOBLOB NUMBER;
V_SEQ VARCHAR2(20);
BEGIN
   IF INSERTING THEN
      IF (:NEW.IDFOTOPRAFIL = '1') THEN
         
         NOVO_CODIGO_NT('FOTOPRATOBLOB', V_SEQ);

         INSERT INTO FOTOPRATOBLOB
         (NRFOTOPRATOBLOB, CDFILAUXPRAT, CDPRATO, IDRECEITA, CDFILIAL, CDSERVFILI, NRFOTO,
          NRORG, CDOPERINCLUSAO, CDOPERULTATU, DTINCLUSAO, NRORGINCLUSAO, NRORGULTATU, DTULTATU)
         SELECT TO_NUMBER(V_SEQ), :NEW.CDFILAUXPRAT, :NEW.CDPRATO, 'F', :NEW.CDFILIAL, NULL, :NEW.NRFOTOPRAFIL,
                :NEW.NRORG, :NEW.CDOPERULTATU, :NEW.CDOPERULTATU, SYSDATE, :NEW.NRORG, :NEW.NRORG, SYSDATE
           FROM DUAL;
      END IF;
   END IF;
   IF UPDATING THEN
      IF ((:OLD.IDFOTOPRAFIL = '1' AND :NEW.IDFOTOPRAFIL IS NULL) OR
          (:OLD.IDFOTOPRAFIL = '2' AND :NEW.IDFOTOPRAFIL = '1')) THEN
         DELETE FROM FOTOPRATOBLOB
          WHERE CDFILAUXPRAT = :OLD.CDFILAUXPRAT
            AND CDPRATO = :OLD.CDPRATO
            AND CDFILIAL = :OLD.CDFILIAL
            AND IDRECEITA = 'F';

         NOVO_CODIGO_NT('FOTOPRATOBLOB', V_SEQ);

         INSERT INTO FOTOPRATOBLOB
         (NRFOTOPRATOBLOB, CDFILAUXPRAT, CDPRATO, IDRECEITA, CDFILIAL, CDSERVFILI, NRFOTO,
          NRORG, CDOPERINCLUSAO, CDOPERULTATU, DTINCLUSAO, NRORGINCLUSAO, NRORGULTATU, DTULTATU)
         SELECT TO_NUMBER(V_SEQ), :NEW.CDFILAUXPRAT, :NEW.CDPRATO, 'F', :NEW.CDFILIAL, NULL, :OLD.NRFOTOPRAFIL,
                :NEW.NRORG, :NEW.CDOPERULTATU, :NEW.CDOPERULTATU, SYSDATE, :NEW.NRORG, :NEW.NRORG, SYSDATE 
           FROM DUAL;
      END IF;
   END IF;
   IF DELETING THEN
      DELETE FROM FOTOPRATOBLOB
          WHERE CDFILAUXPRAT = :OLD.CDFILAUXPRAT
            AND CDPRATO = :OLD.CDPRATO
            AND CDFILIAL = :OLD.CDFILIAL
            AND IDRECEITA = 'F';
   END IF;
END;
/


-- VERSAO --
BEGIN ATUVEROBJ('TRG_UPD_FOTOPRATOFIL','6.25.000'); END;
/