CREATE OR REPLACE TRIGGER ATUALIZA_PATCHVERPROD
AFTER DELETE OR INSERT OR UPDATE
ON PATCHVERS 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 PATCHVERPROD
            (CDPRODGRUP,
             CDVERPRODUTO,
             CDRELVERPROD,
             CDPATCHVERPR,
             DTPATCHVERPR,
             TXPATCHVERPR,
             NRPATCHPROD)
         SELECT
            V_CDPRODGRUP,
            :NEW.CDVERSGSIS,
            :NEW.CDRELEASEVER,
            :NEW.CDPATCHVERS,
            :NEW.DTPATCHVERS,
            :NEW.TXOBSPATVERS,
            :NEW.NRPATCHPRODS
         FROM
            DUAL
         WHERE
            NOT EXISTS (SELECT
                           *
                        FROM
                           PATCHVERPROD
                        WHERE
                           CDPRODGRUP   = V_CDPRODGRUP      AND
                           CDVERPRODUTO = :NEW.CDVERSGSIS   AND
                           CDRELVERPROD = :NEW.CDRELEASEVER AND
                           CDPATCHVERPR = :NEW.CDPATCHVERS);

         UPDATE PATCHVERPROD SET
            DTPATCHVERPR = :NEW.DTPATCHVERS,
            TXPATCHVERPR = :NEW.TXOBSPATVERS,
            NRPATCHPROD  = :NEW.NRPATCHPRODS
         WHERE
            CDPRODGRUP   = V_CDPRODGRUP      AND
            CDVERPRODUTO = :NEW.CDVERSGSIS   AND
            CDRELVERPROD = :NEW.CDRELEASEVER AND
            CDPATCHVERPR = :NEW.CDPATCHVERS;
      ELSE
         DELETE FROM PATCHVERPROD
         WHERE
            CDPRODGRUP   = V_CDPRODGRUP      AND
            CDVERPRODUTO = :OLD.CDVERSGSIS   AND
            CDRELVERPROD = :OLD.CDRELEASEVER AND
            CDPATCHVERPR = :OLD.CDPATCHVERS;
      END IF;
   END IF;
END;
/


