Problemas com trigger (tabela mutante)

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
jks1903
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 188
Registrado em: Qui, 04 Fev 2010 8:08 am

Olá a todos, meu primeiro post nesse forum, rsrs.

Eu criei uma trigger em uma tabela em que seu objetivo e testar o valor de um determinado campo, e satisfazendo essa condição ele automaticamente altera esse valor para um outro valor.
Exemplo:

Eu tenho uma tabela chamada produtos.
Nessa tabela existe um campo chamado cd_setor.

Aí eu criei uma trigger com o evento AFTER INSERT que, se o cd_setor for 10, por exemplo, ele automaticamente muda o código para 5.

Porém quando insiro esse valor nesse campo da tabela, o ORACLE me retorna um erro, dizendo que a tabela é mutante. Isso pelo que eu entendi ocorre porque a ação da trigger é na mesma tabela que dispara o evento.

Alguém tem alguma solçução de como eu possa fazer isso?

Obrigado desde já.
victorhugomuniz
Moderador
Moderador
Mensagens: 1396
Registrado em: Sex, 01 Fev 2008 2:06 pm
Localização: Rio de Janeiro - RJ
Contato:
:D

posta a sua trigger ai

para evitar inconsistencia de dados esse erro foi tratado pela oracle gerando o erro mutante.. quando você tenta consultar um dado que "esta" sendo alterado, este erro é disparado.

acredito que você esteja fazendo um select na tabela para verificar se cd_setor = 10.. caso isso realmente esteja acontecendo você pode alterar sua trigger para usar NEW.CD_SETOR e não ter mais que consultar a tabela.. você pode alterar o dado antes de inserir e não depois de inserir verificar e caso necessario alterar...
jks1903
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 188
Registrado em: Qui, 04 Fev 2010 8:08 am

Selecionar tudo

CREATE OR REPLACE TRIGGER trg_coparticipacao
ALTER INSERT
ON contrato
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
    IF :NEW.cd_grupo_franquia = 116 THEN
    BEGIN
         DECLARE CURSOR duplica_franquias
          IS
          SELECT seq_grupo_franquia.NEXTVAL AS nextvl, gf.cd_grupo_franquia AS atual, gf.ds_grupo_franquia || ' - ' || c.cd_contrato || '[' || gf.cd_grupo_franquia || ']' AS descr, 100 AS v1, 2 AS v2,
                1 AS v3, 3 AS v4, NULL AS n1, NULL AS n2, NULL AS n3, c.cd_contrato AS contr
                FROM grupo_franquia gf, contrato c
                WHERE c.cd_grupo_franquia = gf.cd_grupo_franquia
                AND c.cd_contrato = :NEW.cd_grupo_franquia;



        BEGIN
              
              
              FOR i IN duplica_franquias LOOP

              INSERT INTO grupo_franquia
              VALUES (i.nextvl, i.descr, i.v1, i.v2, i.v3, i.v4, i.n1, i.n2, i.n3);


              DECLARE CURSOR fr_tipoatend IS
              SELECT * FROM grupo_franquia_tipo_atendiment WHERE cd_grupo_franquia = i.atual;
              BEGIN
              FOR i0 IN fr_tipoatend LOOP
                INSERT INTO grupo_franquia_tipo_atendiment
                VALUES(i.nextvl, i0.cd_tipo_atendimento);
              END LOOP;
              END;
              DECLARE CURSOR fr_internacao IS
              SELECT * FROM grupo_franquia_internacao WHERE cd_grupo_franquia = i.atual;
              BEGIN
              FOR a IN fr_internacao LOOP
                  INSERT INTO grupo_franquia_internacao
                  VALUES(a.dt_vigencia, a.considerar, a.nr_dias, a.valor, i.nextvl, seq_grupo_franquia_int.NEXTVAL, a.tp_internacao);
              END LOOP;
              END;

              DECLARE CURSOR itgrupo IS
              SELECT * FROM itgrupo_franquia WHERE cd_grupo_franquia = i.atual;
              BEGIN
                  FOR i2 IN itgrupo LOOP
                  INSERT INTO itgrupo_franquia
                  VALUES(i.nextvl, i2.cd_grupo_procedimento);
                  DECLARE CURSOR excess IS
                  SELECT * FROM franquia_excecao WHERE cd_grupo_franquia = i.atual AND cd_grupo_procedimento = i2.cd_grupo_procedimento;
                  BEGIN
                    FOR ex IN excess LOOP
                        INSERT INTO franquia_excecao
                        VALUES(i.nextvl, ex.cd_grupo_procedimento, ex.cd_procedimento);

                        DECLARE CURSOR vlr_excess IS
                                SELECT * FROM valor_franquia_excecao WHERE cd_grupo_franquia = i.atual AND
                                     cd_grupo_procedimento = ex.cd_grupo_procedimento AND cd_procedimento = ex.cd_procedimento;
                        BEGIN
                            FOR vex IN vlr_excess LOOP
                                INSERT INTO valor_franquia_excecao
                                VALUES(vex.dt_vigencia, i.nextvl, ex.cd_grupo_procedimento, ex.cd_procedimento, vex.vl_percentual,
                                       vex.vl_nominal, vex.vl_limite, vex.vl_fator, vex.cd_tabela);
                            END LOOP;
                        END;
                    END LOOP;
                END;

                DECLARE CURSOR vlr_itgrupo IS
                SELECT * FROM valor_itgrupo_franquia WHERE cd_grupo_franquia = i.atual AND cd_grupo_procedimento = i2.cd_grupo_procedimento;
                BEGIN
                    FOR i3 IN vlr_itgrupo LOOP
                        INSERT INTO valor_itgrupo_franquia
                        VALUES(i3.dt_vigencia, i.nextvl, i2.cd_grupo_procedimento, i3.vl_percentual, i3.vl_nominal, i3.vl_limite, i3.vl_fator, i3.cd_tabela);
                    END LOOP;
                END;
            END LOOP;
        END;

===============================================
UPDATE QUE POSSIVELMENTE GERA O ERRO
===============================================
      
 IF i.contr IS NOT NULL THEN
      UPDATE contrato SET cd_grupo_franquia = To_Number(i.nextvl) WHERE cd_contrato = i.contr;
      
    END IF;
==============================================================================================
    END LOOP;
    
    END;

    END;
    END IF;
END;

Como disse antes, o exemplo com a tabela de produtos era um mero exemplo, e o restante da trigger pode até ser ignorado, porque antes de atualizar o valor no campo, ele tem que duplicar o campo em uma outra tabela e pegar o código novo e inserir dentro da tabela contrato, que é onde a trigger aje.

Acredito que o que esteja ocasionando o erro seja aquele update que eu destauqei ali, porque ele precisa atualizar o registro que está sendo inserido, porém não consigo resolver esse problema.


Obrigado.
Avatar do usuário
madmax
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 293
Registrado em: Qua, 13 Dez 2006 5:02 pm
Localização: São Paulo
Contato:
________________________________
Douglas - Madmax.

Caro colega.

Aqui mesmo no forum tem um topico que fala muito de erro Mutating trigger.

segue o link :

http://glufke.net/oracle/viewtopic.php? ... t=mutating


Abs.
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

O seu cursor duplica_franquias também vai gerar esse erro, pois você não pode fazer select na tabela CONTRATO dentro da trigger dela própria: dentro de um row-level trigger a tabela está durante uma modificação, logo não possui uma visão consistente para o select.

Tem muita lógica na trigger na minha humilde opinião. Eu sinto que seria mais simples que esse código ficasse na procedure que insere o contrato, até porque dessa forma você consegue otimizar para que não seja feito inserts row-by-row (ou slow-by-slow), que torna o desempenho muito ruim.

Além disso a lógica na trigger dificulta a manutenção bastante. Se para você que está fazendo não está simples, imagine para quem pegar esse código daqui a alguns anos? O camarada provavelmente não vai nem saber de onde estão surgindo essas linhas em franquia_excecao, e nas outras tabelas.

Se mesmo assim você quiser manter a lógica em triggers, o resumo da ópera é o seguinte:
- Crie uma package com uma variável de sessão tipo nested table com a estrutura de sua tabela (type abc is table of contrato%rowtype).
- Crie 3 triggers (uma before statement, uma after each row, uma after statement).
- Na before statement você limpa (esvazia) a nested table.
- Na after each row você popula a nested table linha a linha inserindo os valores de :new da sua tabela CONTRATO.
- Na after statement faz toda sua lógica que hoje está nesse seu código. Na after statement você pode já dar um select na CONTRATO, só que para saber quais linhas foram inseridas você precisará ler da nested table onde você guardou os rowids das linhas (ou os dados das colunas em si, dependendo da necessidade).

De novo: é muito mais complicado e muito mais sujeito a erros. Se seu sistema tiver um nível de concorrência razoável, além disso, a existência de inúmeras triggers acaba causando muitos rollbacks implícitos o que pode prejudicar muito o desempenho sem nada que você possa fazer a respeito. (ver referência http://tkyte.blogspot.com/2005/08/somet ... f-iii.html )
jks1903
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 188
Registrado em: Qui, 04 Fev 2010 8:08 am

fsitja, eu sei que realmente fica complexo tudo isso em triggers.

Mas eu preciso fazer isso porque o sistema que temos não tem essa funcionalidade que precisamos utilizar apartir de agora. E como eu não tenho acesso a programação do sistema, a maneira que encontrei de fazer foi via banco mesmo. (na verdade ainda não encontrei, rsrs)

Mas vou testar a tua ideia, porém nunca fiz nada parecido com o que você me sugeriu. Você sabe de algum local onde eu possa ver algum exemplo de uma implementação?

Vlw pela ajuda.
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Beleza, camarada. Segue um exemplo que fiz dia desses num outro forum, apenas ilustrando o processo todo.

Selecionar tudo

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as fsitja
 
SQL> create table tabela_1 (numero number, tipo varchar2(10), campo number(10));
 
Table created
 
SQL> create table tabela_2 (numero number(10), tipo varchar2(10));
 
Table created
 
SQL>
SQL> CREATE OR REPLACE PACKAGE pack_estado_tab_1 IS
  2    type t_nt_tabela_1 is table of tabela_1%rowtype;
  3    nt_tab_1 t_nt_tabela_1 := t_nt_tabela_1(null);
  4  END;
  5  /
 
Package created
 
SQL>
SQL> CREATE OR REPLACE TRIGGER tg_tab_1_ai_r
  2    AFTER INSERT ON tabela_1
  3    FOR EACH ROW
  4  DECLARE
  5    v_rec_tab_1 tabela_1%rowtype;
  6  BEGIN
  7    pack_estado_tab_1.nt_tab_1.extend;
  8    v_rec_tab_1.numero := :new.numero;
  9    v_rec_tab_1.tipo := :new.tipo;
 10    v_rec_tab_1.campo := :new.campo;
 11    pack_estado_tab_1.nt_tab_1(pack_estado_tab_1.nt_tab_1.last) := v_rec_tab_1;
 12  END;
 13  /
 
Trigger created
 
SQL>
SQL> CREATE OR REPLACE TRIGGER tg_tab_1_bi_s
  2    AFTER INSERT ON tabela_1
  3  BEGIN
  4    pack_estado_tab_1.nt_tab_1.delete;
  5  END;
  6  /
 
Trigger created
 
SQL>
SQL> CREATE OR REPLACE TRIGGER tg_tab_1_ai_s
  2    AFTER INSERT ON tabela_1
  3  BEGIN
  4    FOR i IN 1 .. pack_estado_tab_1.nt_tab_1.count
  5    LOOP
  6      INSERT INTO tabela_2
  7        (numero, tipo)
  8      VALUES
  9        (pack_estado_tab_1.nt_tab_1(i).numero, pack_estado_tab_1.nt_tab_1(i).tipo);
 10      UPDATE tabela_1
 11         SET campo = 3
 12       WHERE numero = pack_estado_tab_1.nt_tab_1(i).numero;
 13    END LOOP;
 14  END;
 15  /
 
Trigger created
 
SQL> insert into tabela_1(numero, tipo) values (1, 'A');
 
1 row inserted
 
SQL> insert into tabela_1(numero, tipo) values (2, 'B');
 
1 row inserted
 
SQL> insert into tabela_1(numero, tipo) values (2, 'C');
 
1 row inserted
 
SQL> select * from tabela_1;
 
    NUMERO TIPO             CAMPO
---------- ---------- -----------
         1 A                    3
         2 B                    3
         2 C                    3
 
SQL> select * from tabela_2;
 
     NUMERO TIPO
----------- ----------
          1 A
          2 B
          2 C
 
SQL> rollback;
 
Rollback complete
 
SQL> drop table tabela_1;
 
Table dropped
 
SQL> drop table tabela_2;
 
Table dropped
 
SQL> drop package pack_estado_tab_1;
 
Package dropped
 
SQL> 
Lembro que na trigger "tg_tab_1_ai_s" está a lógica de negócio que você quer implementar. Nesse exemplo a lógica é replicar a inserção da linha da tabela 1 para a tabela 2 e arbitrariamente seta 3 em tal campo da tabela 1 para todas linhas nas quais o where seja satisfeito por uma condição dada.
jks1903
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 188
Registrado em: Qui, 04 Fev 2010 8:08 am

Valeu, cara.

Vou tentar implementar isso na minha idéia aqui.

Vlw mesmo pela ajuda.
jks1903
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 188
Registrado em: Qui, 04 Fev 2010 8:08 am

Opa, estava viajando e por isso não tinha mais mexido com isso.
Mas vamos de volta, rsrs

Seguinte, a replicação ali eu consegui, porem a logica que quero implementar é que não estou conseguindo.
Talvez a trigger que eu postei esteja mesmo muito complexa, mas vou simplificar aqui a lógica que quero fazer, ai se vocês conseguirem me ajudar nesse exemplo, conseguirei colocar em prática.
Assim:

Digamos que eu tnha uma tabela produtos.
Com os campos ID, ds_produto, cd_setor

No momento em que eu insiro uma nova linha, se o cd_setor = 5, eu tenho que alterar ele para 10.

Se eu inserir 1, 'CD-ROM', 5
ele tera que alterar para 1, 'CD-ROM', 10

Porém não estou conseguindo alterar o registro que estou inserindo (se é que isso é possivel).

Mas, desde já obrigado.
diegolenhardt
Moderador
Moderador
Mensagens: 1177
Registrado em: Qui, 15 Out 2009 10:28 am
Localização: Recife

para alterar o proprio campo que esta inserindo, pode fazer:

:NEW.CAMPO := VALOR;

porém, não podera fazer select na propria tabela da trigger para buscar esse "valor", se for outra tabela beleza,
jks1903
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 188
Registrado em: Qui, 04 Fev 2010 8:08 am

ops, eu tentei fazer isso.

Mas o oracle resultou esse erro:

ORA-04084: não pode alterar valores NEW para este tipo de gatilho
diegolenhardt
Moderador
Moderador
Mensagens: 1177
Registrado em: Qui, 15 Out 2009 10:28 am
Localização: Recife

poem o tipo do gatilho como BEFORE INSERT
jks1903
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 188
Registrado em: Qui, 04 Fev 2010 8:08 am

Mas como que posso colocar before?

EU estou tentando com uma trigger instead of insert, porque caso contrario ele me acusa erro de tabela mutante.

o teste que estou tentando implementar é esse:

Selecionar tudo

CREATE OR REPLACE TRIGGER ins_vw
instead OF INSERT ON vw_teste
REFERENCING NEW AS NEW OLD AS OLD
BEGIN
     IF Lower(:NEW.a) = 5 THEN
     BEGIN
         :NEW.a := 10;
     END;
     END IF; 
END;
jks1903
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 188
Registrado em: Qui, 04 Fev 2010 8:08 am

vw_teste é uma view da tabela teste, que tem os campos a e b.
jks1903
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 188
Registrado em: Qui, 04 Fev 2010 8:08 am

Apenas corrigindo o código, postei errado:

Selecionar tudo

CREATE OR REPLACE TRIGGER ins_vw
BEFORE INSERT ON teste
REFERENCING NEW AS NEW OLD AS OLD
BEGIN
     IF :NEW.a = 5 THEN
     BEGIN
         :NEW.a := 10;
     END;
     END IF; 
END;
diegolenhardt
Moderador
Moderador
Mensagens: 1177
Registrado em: Qui, 15 Out 2009 10:28 am
Localização: Recife

Neste tipo de trigger não dá mesmo,
jks1903
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 188
Registrado em: Qui, 04 Fev 2010 8:08 am

Pois é.

Você teria alguma ideia de como eu posso fazer isso então?
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Não só não pode alterar o :new e :old como não pode visualizá-los. Você precisa declarar sua instead of trigger como "for each row" para poder pelo menos ler os :new e :old, mas precisará fazer o insert diretamente na tabela sob a view para mexer nos valores.

A trigger instead of, como diz o nome, executa o bloco PL/SQL "em vez de" realizar o insert que foi realizado sobre a view. Nada está sendo inserido, atualizado ou deletado a menos que você programe e lógica na trigger manualmente. E qualquer DML diretamente na tabela vão escapar à sua lógica da trigger, então é vital tirar grants na tabela de todos usuários da aplicação e dar somente sobre a view.
diegolenhardt
Moderador
Moderador
Mensagens: 1177
Registrado em: Qui, 15 Out 2009 10:28 am
Localização: Recife

Essa duplicação tem necessidade de ser imediata?

Eu tenho uma idéia,

Caso seja tolerável um atraso de 10 minutos, meia hora, etc, você poderia criar um job, que:

a cada vez que disparasse validasse a condicao cd_grupo_franquia = 116,

em uma tabela que a trigger (normal, não precisa ser instead of), iria inserir, exemplo, contrato_duplicacao_temp, etc

o job le essa tabela, faz o que precisa fazer na tabela contrato, e ao final, exclui a linha dessa tabela temporaria,

Existe outro recurso, conhecido como AUTONOMOUS_TRANSACTION, mas pra esse caso acho que não iria dar muito certo, pois acessa sequencial, etc, etc, acho a saida com o JOB uma solução viavel,

=]

Ou então, passe a bola para a aplicação, desenvolvimento
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Acho que passar essa responsabilidade para a aplicação é a melhor solução, sem dúvidas. Uma coisa simples está virando um emaranhado de código que ninguém vai entender o motivo de estar lá e nem querer mexer no futuro.
jks1903
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 188
Registrado em: Qui, 04 Fev 2010 8:08 am

Nada está sendo inserido, atualizado ou deletado a menos que você programe e lógica na trigger manualmente.
_____________________________________________________________

Sim, entendi isso realmente, mas o meu problem é essa dita cuja lógica que não estou conseguindo fazer.

==============================================
Ou então, passe a bola para a aplicação, desenvolvimento .
____________________________________________________________

Meu problema é que não tenho acesso a aplicação, pois o sistema não foi desenvolvido por nós. Temos apenas acesso ao banco de dados...

==========================================

Eu tinha pensado em um job, mas só que não pode demorar meia hora pra atualizar por exemplo, teira que ser algo quase imediato.

Teria que ser a cada 5 minutos no máximo, mas ai eu acredito que vá consumir muitos recursos do servidor.

Hoje, a forma bisonha que estou fazendo é que, cada vez que é cadastrado um novo registro, isso é umas 5 ou 6 vezes ao dia, eu faço um UPDATE manual na tabela.


Existe alguma forma, em algum tipo de trigger em que seja possviel alterar o valor de :NEw?

Tipo, fazer algo como :NEW.a = 5?
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Sim, mas a trigger precisa ser before insert for each row. Diretamente na tabela, e não como instead of. Se for instead of tudo bem, mas você vai ter mais trabalho pois terá que escrever os inserts nela, remover grants da aplicação e substituir todos comandos SQL, INSERT, UPDATE e DELETE da tabela para a view, que você alega que não tem acesso, então já seria um impeditivo para usar Instead Of triggers.
jks1903
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 188
Registrado em: Qui, 04 Fev 2010 8:08 am

MAs é que a questao é a seguinte.
Eu tentei fazer isso diretamente na tabela e deu erro. (erro o qual me motivou a abrir esse topico, rsrs).

O Oracle me retornava um erro de tabela mutante, que acredito ser porque estou tentando alterar o registro q está sendo inserido.

AI eu pesquisei e vi que para evitar isso teria que utilizar trigger com instead off (recurso que nunca havia usado). MAs também sem sucesso.

AI eu tentei por packages como você me sugeriu e a duplicação funcionou blza, mas na hora de alterar o campo da tabela inicial não foi também.

To achando que a solução poderia ser um job mesmo, como me sugeriram em um post anterior.

A ideia é simples mas a logica está complicada, rsrsrs.

Mas obrigado mesmo por estarem me ajudando.
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Vai pelo exemplo que te dei. A instead of não vai adiantar pois você teria que mexer na aplicação.

Para permitir alterar na própriar tabela você só precisa fazer o que o Diegolenhardt falou, que é fazer a trigger como before each row. No exemplo que postei fiz como after mas não interfere a menos que você quisesse o rowid, o que não é o caso. O que der de erros, você posta sua solução e o erro que ocorreu, se foi na compilação, na execução, e qual mensagem.

O job vai tornar a coisa toda assíncrona, o banco de dados vai ficar inconsistente por um tempo e você vai ter o trabalho de gerenciar isso, o que vai gerar ainda mais trabalho. É bem arriscado ocorrer bugs e propagar dados incorretos para outras tabelas e processos se o job não executar no momento certo.
jks1903
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 188
Registrado em: Qui, 04 Fev 2010 8:08 am

Cara, assim.

Eu copiei todo o codigo que você postou pra mim tentar exeutar aqui.
Tudo certo, porem algumas partes do código eu não entendi muito bem.

O principal é essa parte, que você me disse ser onde toda a lógica será implementada:

Selecionar tudo

 CREATE OR REPLACE TRIGGER tg_tab_1_ai_s
      AFTER INSERT ON tabela_1
    BEGIN
      FOR i IN 1 .. pack_estado_tab_1.nt_tab_1.count
      LOOP
        INSERT INTO tabela_2
          (numero, tipo)
        VALUES
          (pack_estado_tab_1.nt_tab_1(i).numero, pack_estado_tab_1.nt_tab_1(i).tipo);
       UPDATE tabela_1
          SET campo = 3
        WHERE numero = pack_estado_tab_1.nt_tab_1(i).numero;
     END LOOP;
   END;
O que eu não entendi é o que ele faz. Porque é necessário esse loop?
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Tenta visualizar o seguinte, pois só vai ser possível resolver isso quando entrar na cabeça o motivo de não estar funcionando. Você precisa entender a razão disso tudo.

Por que ocorre o erro de mutating trigger?

A trigger for each row executa enquanto sua alteração está sendo processada uma vez para cada linha afetada no INSERT/UPDATE/DELETE (referenciadas daqui adiante como comandos de DML - Data Manipulation Language). Ênfase no *enquanto*. Como estamos numa trigger for each row, a tabela ainda está no meio de uma alteração e não há uma visão consistente dos dados. Portanto, só podemos ler ou gravar na dita tabela da trigger após todas as triggers for each row terem terminado de serem executadas.

Durante a trigger for each row imagine que você está olhando no microscópio. Você pode enxergar em detalhes tudo que está acontecendo dentro da modificação no banco de dados, mas no seu campo de visão só dá para ver uma linha. Dessa forma, não há como tirar a cara da lente do microscópio e enxergar a fotografia toda, ou mesmo as linhas "vizinhas" que foram ou ainda serão processadas .

Como ver a "fotografia toda" do BD então?

Na trigger after statement. Toda trigger em que não há declarado "for each row" é classificada automaticamente como uma trigger que executa uma vez por comando. Isso quer dizer que depois de seu DML processar todas as linhas e ter sido bem-sucedido, sem causar erros, a AFTER STATEMENT entra em ação.

Ela é como o "ponto final" da transação, a última coisa que acontece antes do controle voltar para seu programa PL/SQL que disparou o comando de DML.

Como contornar o problema?

Na trigger for each row nós podemos ler os campos :new e :old de cada linha sendo modificada. Na after statement não, pois a alteração já acabou e processou tudo.

Enxergamos a "visão panorâmica" do Banco de Dados, mas não temos mais o microscópio para olhar e enxergar quais linhas foram alteradas pelo DML que acabou de ser executado nem que dados entraram ou saíram.

Beleza... mas e daí?

Como cada uma delas faz uma coisa que queremos, então o pulo do gato é o seguinte é usar a força de cada uma delas:
- Vamos usar a for each row para pegar na "pinça" todas as linhas que nos interessam e guardar no bolso.
- Durante a after statement nós já podemos alterar e dar selects no banco de dados livremente, então nós tiramos nossas linhas que guardamos no bolso com a pinça e trabalhamos nelas uma por vez.

É isso o que o dito loop lá na trigger que você postou acima está fazendo:
Ela pega as linhas que tínhamos guardado anteriormente lá na package, e faz o que precisava ter sido feito, mas que não pôde pois a for each row não era capaz de fazer.


Agora repara no seguinte. Como a trigger for each row está no microscópio, ela pode alterar dados sim. Mas ela está limitada a alterar os campos da linha atual pelo qual a lente do microscópio está passando. Então, se você quer alterar dados só da linha atual, e para isso você não precisa buscar dados de outras linhas para saber qual valor gravar, faça nela mesmo.

Na for each row você *pode* dar um comando DML (insert/update/delete) em outra tabela, mas de novo, você não deveria. O motivo é simples: o microscópio é um só :lol:
Isso quer dizer que se você está alterando a tabela PESSOA e na trigger for each row dela você dá um insert na tabela DEPENDENTE, você acabou de emprestar seu microscópio para a outra trigger for each row da outra tabela. Logo, nesse momento nós acabamo de ficar com DUAS tabelas na fila do microscópio, onde uma linha está alterada simultaneamente em cada tabela. Se a trigger da segunda tabela, a DEPENDENTE, tentar um select ou DML na PESSOA, o BD vai gritar: "TABLE IS MUTATING!" pois a coitada da PESSOA ainda está esperando a DEPENDENTE devolver o microscópio dela e ela terminar o que ela estava fazendo.

Notou a sutileza? Por isso evite selects e DML em triggers for each row. Se isso não convence você ainda, pense que elas são o inferno por conta de performance também, pois precisam disparar linha por linha um comando por vez ao BD, quando o banco de dados é bom mesmo e nasceu para lidar com conjuntos de linhas tudo numa tacada só. Nunca não row-by-row.

Dessa forma, fica um tempo para digerir tudo isso. Veja tudo com atenção, crie um caso de teste bem simples igual eu criei, vá incrementando beeeeem devagar com mais funcionalidades na AFTER STATEMENT conforme seu requisito e sempre entenda o motivo pelo qual a coisa toda parou de funcionar. Não tem mistério.

O erro de mutating trigger é uma coleira que a Oracle coloca em nós para nos impedir de fazer operações inválidas, que deixariam o banco de dados na berlinda.

...

Acho que estou precisando abrir um blog... está devagar hoje.
diegolenhardt
Moderador
Moderador
Mensagens: 1177
Registrado em: Qui, 15 Out 2009 10:28 am
Localização: Recife

Show de bola.. ;)
jks1903
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 188
Registrado em: Qui, 04 Fev 2010 8:08 am

Cara, agora sim.

Na faculdade eu não tinha entendido quando realmente utilizar for each row e quando não.

Agora sim.
Até já resolvi meu problema, mas não utilizando packages porque eu, no meu ver, encontrei uma forma mais facil, tudo isso depois da explicação acima.

Veja o que fiz:

Criei duas triggers no after insert da tabela.
Uma com o for each row e a outra sem o each row.

Criei tambem uma tabela temporaria de um só campo, ID, onde sempre terá apenas um registro.

Ai, na trigger que tem o for each row eu fiz ela inserir nessa tempo o rowID da linha que está sendo inserida. E apenas isso.

Após isso, lá na trigger After statement eu criei apenas um Update, onde ele atualiza o campo que eu quero para o valor desejado onde o rowid da linha for igual ao rowid armazenado na tabela.

Ao fim desse processo, eu limpo a tabela temp.

Para não postar todo o código que fiz, com minha verdadeira aplicação, eu vou postar o teste que fiz com uma tabela chamada teste1.

Essa tabela teste1 tem os campos campo e campo2.

Selecionar tudo


-- essa é a tabela temporária
CREATE TABLE temp_id
              (id VARCHAR(30));


--essa é a trigger com o for each row, que insere o rowid na temp
CREATE OR REPLACE TRIGGER trg_teste1
 AFTER INSERT ON teste1
 REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
 DECLARE
 BEGIN
     IF :NEW.campo = '5' THEN
     BEGIN
          INSERT INTO temp_id
          VALUES(:NEW.ROWID);
     END;
     END IF;
 END;

--e por fim essa é a trigger que resolve o meu problema, onde é feito o update.
CREATE OR REPLACE TRIGGER trg2_teste1
 AFTER INSERT ON teste1
 REFERENCING NEW AS NEW OLD AS OLD
 BEGIN
     UPDATE teste1 SET campo = '5505'
     WHERE ROWID = (SELECT id FROM temp_id WHERE ROWNUM < 2);
     DELETE FROM temp_id;
 END;
Olha que eu sofri hein.

Mas consegui. Esse método funciona, porque no sistema o usuário não consegue inserir mais de um registro por vez...

muito obrigado mesmo, vlw. Tava quase desistindo, rsrs. :D
diegolenhardt
Moderador
Moderador
Mensagens: 1177
Registrado em: Qui, 15 Out 2009 10:28 am
Localização: Recife

não li tudo e nem vi tudo, mas acho que o cara fazia loop por que fazia sem each row, ai gravava todas as linhas modificadas na transacao inteira,

;)
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Beleza, cara. Era isso mesmo :-o

Onde eu trabalhei antes o "método oficial" da empresa era através de tabela temporária também, e funciona perfeitamente. Só precisa ter atenção com commits indesejados ou fora de hora, para sempre limpar a tabela temporária devidamente depois, ou pode dar bug se alguém der select na linha que não foi ele quem inseriu.

Eu particularmente prefiro package porque ele cuida disso automaticamente pois possui escopo apenas para a sessão atual, então é à prova de falhas, bugs ou mal-usos.

O loop era só mesmo para casos onde um insert faria inclusão de mais de uma linha de uma só vez mesmo.

Parabéns por ter elaborado a solução sozinho, muito bacana mesmo. :wink:

Abraço,
Francisco.
jks1903
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 188
Registrado em: Qui, 04 Fev 2010 8:08 am

Abraço,
você é fera mesmo.

Vlw pela força.
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 7 visitantes