CREATE OR REPLACE TRIGGER USR_EXCLUSAO_FORNECEDOR
AFTER DELETE ON FORNECEDOR FOR EACH ROW
DECLARE
  V_NOVOCODIGO    INTEGER;
BEGIN
   /* Retorna o sequencial para a tabela */
   BEGIN
      SELECT W3_IDFORN.NEXTVAL
        INTO V_NOVOCODIGO
        FROM DUAL;
   EXCEPTION
   WHEN NO_DATA_FOUND THEN
      V_NOVOCODIGO := 1;
   END;

   FOR C_USR_ZW3 IN (
   SELECT V_NOVOCODIGO AS W3_IDFORN, W3_NOME, W3_NREDUZ, W3_TIPO, W3_END, W3_BAIRRO,
          W3_CEP, W3_MUN, W3_PAIS, W3_EST, W3_TEL, W3_TELEX, W3_FAX,
          W3_CONTATO, W3_CGC, W3_INSCR, W3_INSCRM, W3_COND, W3_EMAIL,
          W3_TPEDI, W3_COD_MUN, W3_ENDCOMP, W3_IBGE, W3_BANCO, W3_AGENCIA,
          W3_DESCCOM, W3_PERVIGE, W3_COMPLEM, W3_TIPPAG, W3_NUMCON
     FROM USR_ZW3
    WHERE W3_CGC = :OLD.NRINSJURFORN
      AND W3_IDFORN = (SELECT MAX(W3_IDFORN) FROM USR_ZW3 WHERE W3_CGC = :OLD.NRINSJURFORN)) LOOP

      INSERT INTO USR_ZW3
         (W3_IDFORN, W3_NOME, W3_TIPO,
          W3_END, W3_BAIRRO, W3_CEP,
          W3_PAIS, W3_MUN, W3_EST,
          W3_TEL, W3_TELEX, W3_FAX, W3_CONTATO,
          W3_CGC, W3_INSCR, W3_INSCRM, W3_COND, W3_EMAIL, W3_TPEDI,
          W3_NREDUZ, W3_COD_MUN, W3_HRGRV, W3_DTGRV,
          W3_ENDCOMP, W3_IBGE, W3_BANCO, W3_AGENCIA,
          W3_DESCCOM, W3_PERVIGE, W3_TIPPAG, W3_COMPLEM, W3_NUMCON)
       VALUES
         (C_USR_ZW3.W3_IDFORN, C_USR_ZW3.W3_NOME, C_USR_ZW3.W3_TIPO,
          C_USR_ZW3.W3_END, C_USR_ZW3.W3_BAIRRO, C_USR_ZW3.W3_CEP,
          C_USR_ZW3.W3_PAIS, C_USR_ZW3.W3_MUN, C_USR_ZW3.W3_EST,
          C_USR_ZW3.W3_TEL, C_USR_ZW3.W3_TELEX, C_USR_ZW3.W3_FAX, C_USR_ZW3.W3_CONTATO,
          C_USR_ZW3.W3_CGC, C_USR_ZW3.W3_INSCR, C_USR_ZW3.W3_INSCRM, C_USR_ZW3.W3_COND, C_USR_ZW3.W3_EMAIL, '2',
          C_USR_ZW3.W3_NREDUZ, C_USR_ZW3.W3_COD_MUN, TO_CHAR(SYSDATE, 'HH24MI'), TRUNC(SYSDATE),
          C_USR_ZW3.W3_ENDCOMP, C_USR_ZW3.W3_IBGE, C_USR_ZW3.W3_BANCO, C_USR_ZW3.W3_AGENCIA,
          C_USR_ZW3.W3_DESCCOM, C_USR_ZW3.W3_PERVIGE, C_USR_ZW3.W3_TIPPAG, C_USR_ZW3.W3_COMPLEM, C_USR_ZW3.W3_NUMCON);
   END LOOP;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('USR_EXCLUSAO_FORNECEDOR','5.09.000'); END;
/

