Menu de navegação

[GirinoParticipante]

CREATE OR REPLACE TRIGGER INSERT_TABELA
AFTER INSERT ON TABELA_1 FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

BEGIN
INSERT INTO TABELA_2 VALUES (:new.tipo, :new.numero);

COMMIT;
END;

BEGIN
UPDATE TABELA_1
SET CAMPO = 3
WHERE NUMERO = :new.numero;
COMMIT;
END;

END;

A minha dúvida é a seguinte: 🙄
Estou tentando inserir na TABELA_2 e depois na mesma trigger dar um update de atualização na TABELA_1 com o numero inserido.
O que está ocorrendo é que quando eu executo a inserção ele faz somente o primeiro bloco do programa, ou seja, ele insere os dados na minha TABELA_2, porem não atualiza a minha TABELA_1 com o update.

O que pode ser? Não sei mesmo, pois a trigger também não gera nenhum erro..!!

Desde já muito Obrigado a quem puder ajudar..!!

Renato

[burgaParticipante]

O que ocorre é que como você está utilizando uma transação autônoma, a sessão que iniciou a trigger ainda não comitou o dado do insert na tabela_1, o seu commit que está dentro da trigger só está comitando o insert da tabela_2.

Ou seja, pra sua transação autônoma, o registro que disparou a trigger ainda não existe… Ele executa o update normalmente, porém não encontra o registro inserido na tabela_1 pois a transação que disparou a trigger ainda não foi comitada…

[GirinoParticipante]

OK.. Entendi…!!

Porém como eu resolvo então..!! 🙄

Muito Obrigado..!! 😀

[fsitjaParticipante]

Você não pode usar pragma autonomous transaction para isso, esse recurso não foi criado para contornar ORA-04091 (mutating trigger).

O jeito correto é criar 3 triggers:
– uma before/after each row gravando as linhas afetadas numa nested table de uma package. Vai ser after ou before dependendo do que você quer fazer.
– uma trigger after statement que coleta da nested table o conjunto de linhas afetadas e faz um loop processando sua lógica (no seu caso updates).
– uma trigger para inicializar a nested table e limpá-la antes de cada statement.

No Oracle 11g existe o recurso de compound trigger, onde você faz tudo numa trigger composta, que pode possuir todos os momentos (before/after, for each row/statement). A lógica é a mesma acima.

Aqui tem a cópia do artigo do Tom Kyte solucionando o problema:
http://glufke.net/oracle/viewtopic.php?t=96

Seguem alguns artigos relacionados.
Sobre pragma autonomous transaction:
http://www.orafaq.com/node/1915
Sobre erro de mutating trigger:
http://asktom.oracle.com/pls/asktom/f?p … 9487119866


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>


[burgaParticipante]

Não sei se serve pra você, mas o que pode fazer no caso de só precisar alterar o registro que está sendo inserido na tabela_1 é o seguinte:

CREATE OR REPLACE TRIGGER INSERT_TABELA
BEFORE INSERT ON TABELA_1 FOR EACH ROW
BEGIN

INSERT INTO TABELA_2 VALUES (:new.tipo, :new.numero);

:NEW.CAMPO := 3; -- seta novo valor no campo desejado do registro que está sendo inserido

END;
/

Fica bem mais simples desse jeito… Escrevi rapidão, então se tiver algum erro, me perdoe…
😀

[GirinoParticipante]

ok..!!

Tentei dessa maneira e está me retornando um erro:

is invalid and failed re-validation

abraço,

Renato

[fsitjaParticipante]

A sugestão do Burga seria para o caso do campo NUMERO ser pk da tabela, pois nunca seria atualizado mais de um registro nessa situação.

Se for isso, desconsidere tudo que postei, por gentileza, pois você nem precisa fazer um update…

[burgaParticipante]

[quote=”Girino”:2f2sfhju]ok..!!

Tentei dessa maneira e está me retornando um erro:

is invalid and failed re-validation

abraço,

Renato[/quote]

Bom alguns dos caminhos possíveis foram apontados, agora acredito que seja mais com você mesmo… Erro de compilação, pelo menos pra trigger que eu passei, não deve ser difícil de arrumar.

Caso seja necessário alterar outros registros que não o que disparou a trigger, vá pelo que o Francisco te indicou, os links que ele postou também são bem úteis, principalmente o site do asktom.

Creio que se alguém mais tiver outras soluções também responderão seu tópico.

Qualquer coisa posta aqui denovo!

[GirinoParticipante]

Olá Pessoal..!! 8)

Resolvi fazer com Procecure e tá fazendo certinho. 😉

Tem como fazer uma trigger de insert que execute essa procedure ❓

Muito Obrigado mesmo pela atenção de todos aqui do Fórum..!! 😉

Renato

[fsitjaParticipante]

Se a procedure for chamada pela trigger você vai voltar ao problema inicial, pois é a mesma coisa só que numa subrotina.

[GirinoParticipante]

Nossa, é verdade, fiz os testes e rodando a procedure individualmente rodou belezinha, porém quando adicionei a trigger para executar a procedure voltou a ocorrer o erro anterior.!! 😆

😥 😥 😥 😥 😥 😥 😥

[IshiiParticipante]

[quote=”Girino”:ib80cwup]CREATE OR REPLACE TRIGGER INSERT_TABELA
AFTER INSERT ON TABELA_1 FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

BEGIN
INSERT INTO TABELA_2 VALUES (:new.tipo, :new.numero);

COMMIT;
END;

BEGIN
UPDATE TABELA_1
SET CAMPO = 3
WHERE NUMERO = :new.numero;
COMMIT;
END;

END;

A minha dúvida é a seguinte: 🙄
Estou tentando inserir na TABELA_2 e depois na mesma trigger dar um update de atualização na TABELA_1 com o numero inserido.
O que está ocorrendo é que quando eu executo a inserção ele faz somente o primeiro bloco do programa, ou seja, ele insere os dados na minha TABELA_2, porem não atualiza a minha TABELA_1 com o update.

O que pode ser? Não sei mesmo, pois a trigger também não gera nenhum erro..!!

Desde já muito Obrigado a quem puder ajudar..!!

Renato[/quote]

Olá,

Basicamente o pragma AUTONOMOUS_TRANSACTION cria uma nova sessão dentro do código PL para que o erro de table is mutating seja evitado, ou seja, fazer um update na tabela que está vinculada a trigger de update… (seria um loop infinito). Na procedure que você criou isso funcionaria pois o gatilho de update (da trigger nela mesma) não existe ainda…

A solução para esse caso é mesmo a criação de 3 triggers conforme apresentado, e particularmente, não vejo outra forma de se resolver isso. Alías antes da versão 8i, esse pragma nem existia e a única forma de se resolver isso era com as triggers…

[]s Ishii

[fsitjaParticipante]

O pragma autonomous transaction deve ser o recurso mais coitado do Oracle, porque 95% das vezes é usado de forma imprópria. 😆

[GirinoParticipante]

Olá fsitja e Amigos do Fórum
Bom Dia à Todos..!! 8)

Estou tentando fazer conforme passado aqui no fórum, criando 1 package e 3 triggers.
Só que não estou conseguindo fazer rodar e ainda por cima a tabela (PCWMSOUTPUT) fica locada.
Alguém poderia dar uma olhada e verificar onde continuo errando.

Desde já muito obrigado..!!

---

Package:

CREATE OR REPLACE PACKAGE pack_estado_tab_1 IS
type t_nt_tabela_1 is table of PCWMSOUTPUT%rowtype;
nt_tab_1 t_nt_tabela_1 := t_nt_tabela_1(null);
END;

---

Trigger_1:

CREATE OR REPLACE TRIGGER T_PRIMEIRA
AFTER INSERT ON PCWMSOUTPUT FOR EACH ROW
DECLARE
v_rec_tab_1 PCWMSOUTPUT%rowtype;

BEGIN
pack_estado_tab_1.nt_tab_1.extend;
v_rec_tab_1.tipo := :new.tipo;
v_rec_tab_1.numero := :new.numero;
v_rec_tab_1.codprod := :new.codprod;
v_rec_tab_1.codcli := :new.codcli;
v_rec_tab_1.codfornec := :new.codfornec;
v_rec_tab_1.codfilial := :new.codfilial;
v_rec_tab_1.qtsep := :new.qtsep;
v_rec_tab_1.qtrec := :new.qtrec;
v_rec_tab_1.qtava := :new.qtava;
v_rec_tab_1.qtcor := :new.qtcor;
v_rec_tab_1.dtemissao := :new.dtemissao;
v_rec_tab_1.semaforo := 3;
v_rec_tab_1.dtprocessamento := :new.dtprocessamento;
v_rec_tab_1.numlote := :new.numlote;
v_rec_tab_1.dtfabricacao := :new.dtfabricacao;

 pack_estado_tab_1.nt_tab_1(pack_estado_tab_1.nt_tab_1.last) := v_rec_tab_1;

END;

---

Trigger_2:

CREATE OR REPLACE TRIGGER T_SEGUNDA
AFTER INSERT ON PCWMSOUTPUT
BEGIN
pack_estado_tab_1.nt_tab_1.delete;
END;

---

Trigger_3:

CREATE OR REPLACE TRIGGER T_TERCEIRA
AFTER INSERT ON PCWMSOUTPUT

BEGIN
FOR i IN 1 .. pack_estado_tab_1.nt_tab_1.count
LOOP

  INSERT INTO "sh_pcwmsoutput"@logixteste 
        VALUES ( pack_estado_tab_1.nt_tab_1(i).tipo,              pack_estado_tab_1.nt_tab_1(i).numero,
                 pack_estado_tab_1.nt_tab_1(i).codprod,           pack_estado_tab_1.nt_tab_1(i).codcli,
                 pack_estado_tab_1.nt_tab_1(i).codfornec,         pack_estado_tab_1.nt_tab_1(i).codfilial,
                 pack_estado_tab_1.nt_tab_1(i).qtsep,             pack_estado_tab_1.nt_tab_1(i).qtrec,
                 pack_estado_tab_1.nt_tab_1(i).qtava,             pack_estado_tab_1.nt_tab_1(i).qtcor,
                 pack_estado_tab_1.nt_tab_1(i).dtemissao,         pack_estado_tab_1.nt_tab_1(i).semaforo,
                 pack_estado_tab_1.nt_tab_1(i).dtprocessamento,   pack_estado_tab_1.nt_tab_1(i).numlote,
                 pack_estado_tab_1.nt_tab_1(i).dtfabricacao,      0);


   UPDATE PCWMSOUTPUT
      SET SEMAFORO = 3
    WHERE numero = pack_estado_tab_1.nt_tab_1(i).numero;
 END LOOP;

END;

[IshiiParticipante]

Olá,

A primeira trigger deveria de BEFORE, ou seja, ANTES do insert para alimentar a pilha da package (trigger_1) acho também que você poderia usar o SEMAFORO como :new.SEMAFORO := 3 em vez do update da trigger_3, pois com certeza é ele que está lockando a tabela.

[]s Ishii

[Autor]

Posts