CREATE OR REPLACE TRIGGER DTULTATU_CONFTELACAIXA_SGTVND
BEFORE INSERT OR UPDATE
ON CONFTELACAIXA FOR EACH ROW
DECLARE
   V_NRCONFTELA VARCHAR2(05);
   V_CDFILIAL VARCHAR2(04);
BEGIN
   IF INSERTING OR UPDATING THEN
      V_NRCONFTELA := NVL(:NEW.NRCONFTELA, :OLD.NRCONFTELA);
      V_CDFILIAL := NVL(:NEW.CDFILIAL, :OLD.CDFILIAL);

      UPDATE SUGESTAOVENDA
         SET DTULTATU = SYSDATE
       WHERE CDFILIAL = V_CDFILIAL
         AND NRCONFTELA = V_NRCONFTELA;

      UPDATE SUGESTAOITEM I
         SET DTULTATU = SYSDATE
       WHERE EXISTS (SELECT 1
                       FROM SUGESTAOVENDA V
                      WHERE V.IDSUGESTAOVENDA = I.IDSUGESTAOVENDA
                        AND V.CDFILIAL        = V_CDFILIAL
                        AND V.NRCONFTELA      = V_NRCONFTELA);

   END IF;
END;
/