ORA-04091 – Trigger mutante!

Fala pessoal, essa semana uma amiga estava pedindo ajuda sobre um erro em uma trigger que ela estava desenvolvendo, o famoso erro X-MAN, trigger mutante.
Este erro ocorre quando criamos uma trigger para determinada tabela no banco de dados e, dentro dela, tentamos manipular os dados dessa mesma tabela, enfrentamos o problema de trigger mutante, o código de erro Oracle é ORA-04091.

Trigger com problema:

CREATE OR REPLACE TRIGGER TG_INSERT_UPDATE_FUNCIONARIO
AFTER INSERT OR UPDATE ON TB_FUNCIONARIO
FOR EACH ROW
DECLARE
v_qtd_cpf number := 0;
BEGIN
SELECT COUNT(*) INTO v_qtd_cpf FROM tb_funcionario WHERE cpf = to_char(:NEW.CPF);
IF (v_qtd_cpf > 0) THEN
RAISE_APPLICATION_ERROR(-20001,’Atenção: Já existe esse CPF em nossa base de dados.!’);
END IF;
END;

Para resolver este problema fui pesquisar na documentação da Oracle e encontrei a solução utilizando essa propriedade PRAGMA AUTONOMOUS_TRANSACTION.

O PRAGMA AUTONOMOUS_TRANSACTION muda a forma de um subprograma funciona dentro de uma transação. Um subprograma marcado com este pragma pode fazer operações SQL e confirmar ou reverter essas operações, sem confirmar ou reverter os dados da transação principal. Para mais informações: http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/autonotransaction_pragma.htm

Trigger com a utilização desta propriedade:

CREATE OR REPLACE TRIGGER TG_INSERT_UPDATE_FUNCIONARIO
AFTER INSERT OR UPDATE ON TB_FUNCIONARIO
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
v_qtd_cpf number := 0;
BEGIN
SELECT COUNT(*) INTO v_qtd_cpf FROM tb_funcionario WHERE cpf = to_char(:NEW.CPF);
IF (v_qtd_cpf > 0) THEN
RAISE_APPLICATION_ERROR(-20001,’Atenção: Já existe esse CPF em nossa base de dados.!’);
END IF;
END;

Anúncios

2 comentários sobre “ORA-04091 – Trigger mutante!

  1. Oi César,

    Como essa trigger está usando AUTONOMOUS_TRANSACTION (sem confirmar ou reverter os dados da transação principal, certíssima essa definição),
    então o select da trigger não “visualiza” as alterações sendo feitas
    enquanto não houver commit na tabela, e, pior,
    isso é verdade até para alterações sendo feitas na mesma sessão!
    Vide os exemplos abaixo.

    Posso estar enganado, mas entendo que essa estratégia usando AUTONOMOUS_TRANSACTION só é segura num ambiente com as seguintes garantias:
    i) se houver commit imediatamente após cada alteração (insert, update, delete) e
    ii) se cada alteração inserir/atualizar apenas um único registro por vez.

    drop table tb_funcionario;

    create table tb_funcionario
    (func_id number(4)
    ,cpf number(4)
    ,nome varchar2(100)
    ,constraint tb_funcionario_pk primary key (func_id)
    );

    CREATE OR REPLACE TRIGGER TG_INSERT_UPDATE_FUNCIONARIO
    AFTER INSERT OR UPDATE ON TB_FUNCIONARIO
    FOR EACH ROW
    DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_qtd_cpf number := 0;
    BEGIN
    SELECT COUNT(*) INTO v_qtd_cpf FROM tb_funcionario WHERE cpf = to_char(:NEW.CPF);
    IF (v_qtd_cpf > 0) THEN
    RAISE_APPLICATION_ERROR(-20001,’Atenção: Já existe esse CPF em nossa base de dados.!’);
    END IF;
    END;
    /

    /* Primeiro exemplo */
    insert into tb_funcionario(func_id, cpf, nome)
    values (1, 1, ‘Primeiro Funcionario com CPF “1”‘);

    insert into tb_funcionario(func_id, cpf, nome)
    values (2, 1, ‘Segundo Funcionario com CPF “1”‘);

    insert into tb_funcionario(func_id, cpf, nome)
    values (3, 1, ‘Terceiro Funcionario com CPF “1”‘);

    select * from tb_funcionario order by cpf, func_id;
    /* neste ponto já existem 3 registros com CPF 1 */

    commit;

    insert into tb_funcionario(func_id, cpf, nome)
    values (4, 1, ‘Quarto Funcionario com CPF “1”‘);
    /* ok! Após o commit a verificação foi efetiva. **/

    — fazendo inserção de múltiplos registros
    insert into tb_funcionario(func_id, cpf, nome)
    select *
    from (
    select 21, 2, ‘Multiplos Funcionarios com CPF “2”‘ from dual union all
    select 22, 2, ‘Multiplos Funcionarios com CPF “2”‘ from dual union all
    select 23, 2, ‘Multiplos Funcionarios com CPF “2”‘ from dual union all
    select 24, 2, ‘Multiplos Funcionarios com CPF “2”‘ from dual
    );

    select * from tb_funcionario order by cpf, func_id;
    /* neste ponto já existem 4 registros com CPF “2” */

    commit;

    update tb_funcionario set cpf=3;

    select * from tb_funcionario order by cpf, func_id;
    /* neste ponto existem 7 registros com CPF “3” */

    commit;

    ps: fui muito otimista com relação ao tamanho das colunas 🙂

    Uma discussão bem interessante sobre como tratar o ORA-04091 verificando valores :NEW e :OLD
    pode ser encontrada em
    http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936

    Porém, se for necessário alterar os valores em :NEW antes de inserir, a solução discutida no link acima não é viável.

    Dá uma conferida, será que me enganei em algum detalhe?

    Baita abraço
    Tilpa

  2. Claro estou de acordo com o seu comentário, essa solução foi para uma trigger que apenas valida uma determinada ação.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s