CREATE OR REPLACE TRIGGER ATUALIZA_RELEVERPROD
AFTER DELETE OR INSERT OR UPDATE
ON RELEASEVERS FOR EACH ROW

DECLARE
   V_CDPRODGRUP GRUPSIST.CDPRODGRUP%TYPE;

BEGIN

   IF INSERTING OR UPDATING THEN
      SELECT
         CDPRODGRUP
      INTO
         V_CDPRODGRUP
      FROM
         GRUPSIST
      WHERE
         CDGRUPSIST = :NEW.CDGRUPSIST;
   ELSE
      SELECT
         CDPRODGRUP
      INTO
         V_CDPRODGRUP
      FROM
         GRUPSIST
      WHERE
         CDGRUPSIST = :OLD.CDGRUPSIST;
   END IF;

   IF (V_CDPRODGRUP IN ('0000000078', '0000000102', '0000000103', '0000000104', '0000000105')) THEN
      IF NOT DELETING THEN
         INSERT INTO RELEVERPROD
            (CDPRODGRUP,
             CDVERPRODUTO,
             CDRELVERPROD,
             DTRELVERPROD,
             TXRELVERPROD)
         SELECT
            V_CDPRODGRUP,
            :NEW.CDVERSGSIS,
            :NEW.CDRELEASEVER,
            :NEW.DTRELEASEVER,
            :NEW.TXOBSRELEVER
         FROM
            DUAL
         WHERE
            NOT EXISTS (SELECT
                           *
                        FROM
                           RELEVERPROD
                        WHERE
                           CDPRODGRUP   = V_CDPRODGRUP    AND
                           CDVERPRODUTO = :NEW.CDVERSGSIS AND
                           CDRELVERPROD = :NEW.CDRELEASEVER);

         UPDATE RELEVERPROD SET
            DTRELVERPROD = :NEW.DTRELEASEVER,
            TXRELVERPROD = :NEW.TXOBSRELEVER
         WHERE
            CDPRODGRUP   = V_CDPRODGRUP    AND
            CDVERPRODUTO = :NEW.CDVERSGSIS AND
            CDRELVERPROD = :NEW.CDRELEASEVER;
      ELSE
         DELETE FROM RELEVERPROD
         WHERE
            CDPRODGRUP   = V_CDPRODGRUP    AND
            CDVERPRODUTO = :OLD.CDVERSGSIS AND
            CDRELVERPROD = :OLD.CDRELEASEVER;
      END IF;
   END IF;
END;
/
