Problemas com trigger (tabela mutante)
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á.
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á.
-
- Moderador
- Mensagens: 1396
- Registrado em: Sex, 01 Fev 2008 2:06 pm
- Localização: Rio de Janeiro - RJ
- Contato:
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...
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...
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;
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.
- madmax
- Rank: DBA Pleno
- Mensagens: 293
- Registrado em: Qua, 13 Dez 2006 5:02 pm
- Localização: São Paulo
- Contato:
________________________________
Douglas - Madmax.
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.
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.
- fsitja
- 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
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 )
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 )
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.
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.
- fsitja
- 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
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.
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.
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>
Valeu, cara.
Vou tentar implementar isso na minha idéia aqui.
Vlw mesmo pela ajuda.
Vou tentar implementar isso na minha idéia aqui.
Vlw mesmo pela ajuda.
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.
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.
-
- 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,
:NEW.CAMPO := VALOR;
porém, não podera fazer select na propria tabela da trigger para buscar esse "valor", se for outra tabela beleza,
ops, eu tentei fazer isso.
Mas o oracle resultou esse erro:
ORA-04084: não pode alterar valores NEW para este tipo de gatilho
Mas o oracle resultou esse erro:
ORA-04084: não pode alterar valores NEW para este tipo de gatilho
-
- Moderador
- Mensagens: 1177
- Registrado em: Qui, 15 Out 2009 10:28 am
- Localização: Recife
poem o tipo do gatilho como BEFORE INSERT
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:
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:
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;
vw_teste é uma view da tabela teste, que tem os campos a e b.
Apenas corrigindo o código, postei errado:
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;
-
- Moderador
- Mensagens: 1177
- Registrado em: Qui, 15 Out 2009 10:28 am
- Localização: Recife
Neste tipo de trigger não dá mesmo,
Pois é.
Você teria alguma ideia de como eu posso fazer isso então?
Você teria alguma ideia de como eu posso fazer isso então?
- fsitja
- 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
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.
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.
-
- 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
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
- fsitja
- 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
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.
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?
_____________________________________________________________
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?
- fsitja
- 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
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.
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.
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.
- fsitja
- 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
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.
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.
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:
O que eu não entendi é o que ele faz. Porque é necessário esse loop?
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:
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;
- fsitja
- 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
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ó
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.
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ó
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.
-
- Moderador
- Mensagens: 1177
- Registrado em: Qui, 15 Out 2009 10:28 am
- Localização: Recife
Show de bola..
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.
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
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.
-- 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;
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
-
- 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,
- fsitja
- 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
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
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.
Abraço,
Francisco.
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.
Abraço,
Francisco.
Abraço,
você é fera mesmo.
Vlw pela força.
você é fera mesmo.
Vlw pela força.
-
- Informação
-
Quem está online
Usuários navegando neste fórum: Nenhum usuário registrado e 5 visitantes