Evitando o Mutating na trigger

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
Avatar do usuário
tfg
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 83
Registrado em: Seg, 03 Mai 2004 3:38 pm
Localização: Novo Hamburgo - RS

Achei essa dica abaixo sobre evitar Mutating nesse site de Oracle:
http://www.linhadecodigo.com.br/dicas.a ... 991&sub=57

Será que resolve mesmo?

Selecionar tudo

Resolvendo erro de mutante em uma Trigger a partir da versão do Banco Dados Oracle8i. Isso ocorre quando você precisa acessar a tabela onde esta a trigger.

Prompt TRIGGER TRIGGER_01_BI
Create Or Replace Trigger TRIGGER_01_BI
Before Insert Or Update
   On TAB_ENDERECOS
Referencing
New As New
Old AS Old
For Each Row
Declare
  ---------------------------------------
  -- Linha que elimina o erro MUTATING --
  ---------------------------------------
  Pragma Autonomous_Transaction;
  ---------------------------
  -- Sequencia do Endereco --
  ---------------------------
  nSequencia := 0;
Begin
  Select Max( Endereco_Id ) + 1
    Into nSequencia
    From TAB_ENDERECOS
   Where Cliente_Id = :New.Cliente_Id;
  :New.Endereco_Id := nSequencia;
End;
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

ESTÁ REDONDAMENTE ERRADO!

Vamos aplicar e ver o porque essa dica é furada!
1. Vamos criar a tabela usada:

Selecionar tudo

create table tab_enderecos  
( cliente_id    number(10)
, endereco_id   number(10)
, endereco      varchar2(100)
);
2. DE CARA, já vi que vai dar erro na trigger por 2 motivos:
(a). Erro de sintaxe na declaração da variável. Deveria ser:

Selecionar tudo

  nSequencia number:= 0;
(b). Não tem exception. Vai dar erro caso a tabela estiver vazia ou for o primeiro registro inserido do CLIENTE_ID..

3. Por isso, vamos inserir agora o cliente 1 (pra evitar o erro).

Selecionar tudo

SQL> insert into tab_enderecos values (1, 1, 'meu endereço');

1 row created.
SQL> COMMIT;
4. Agora, aplique a trigger - corrigindo o erro da declaração que eu citei no item 3.

5. OK! Pronto pra testar! A trigger promete que vai colocar o ENDERECO_ID automático!!! É o que veremos:

Selecionar tudo

SQL> insert into tab_enderecos values (1, null, 'meu endereço');

1 row created.

SQL> select * from tab_enderecos;

CLIENTE_ID ENDERECO_ID ENDERECO
---------- ----------- ------------------------
         1           1 meu endereço
         1           2 meu endereço

2 row selected.
Ué? funcionou? SIM, porque o primeiro foi comitado! Se você rodar outro insert sem ter comitado o anterior, já vai dar erro. Isso porque a Autonomous Transaction funciona em uma outra sessão, ou seja, não enxerga o que você acabou de inserir. Por isso, essa dica está totalmente furada. Veja o que acontece se eu inserir uma linha sem comitar a anterior:

Selecionar tudo

SQL> insert into tab_enderecos values (1, null, 'meu endereço');

1 row created.

SQL> select * from tab_enderecos;

CLIENTE_ID ENDERECO_ID ENDERECO
---------- ----------- ------------------------
         1           1 meu endereço
         1           2 meu endereço
         1           2 meu endereço

3 row selected.
DUPLICOU o ID 2 !!!!! ERRO!!!

Deve-se ter muito cuidado ao usar esse tipo de transação, pois você pode estar fazendo uma grande bobagem e detonando a informação da sua base. (como no exemplo citado na "dica" acima)
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Agora sim... uma forma que FUNCIONA de evitar mutating...
Vamos usar triggers INSTEAD-OF !!!

O exemplo abaixo, cria uma trigger INSTEAD-OF para a VIEW emp2 --> que é uma cópia da emp!

Selecionar tudo

create or replace view emp2 as
select * from emp
Agora vamos criar uma trigger que faz um SELECT na EMP. (ou seja, algo que normalmente daria mutating.)

Selecionar tudo

create or replace trigger trg_emp2
  instead of insert on emp2  
  for each row
declare
  vtemp number;
begin

  --busca a soma do salario!! (aqui já daria mutating!!!)
  select sum(sal)
  into vtemp
  from emp;

  --insere na tabela com a soma buscada acima
  if inserting
  then
    insert
    into emp
    (empno
    ,ename
    ,sal
    )
    values
    (:new.empno
    ,:new.ename
    ,vtemp
    );
  end if;
end trg_emp2;
Vamos fazer um teste:

Selecionar tudo

dbglufke:SCOTT> insert into emp2(empno,ename) values (124,'aaa');

1 linha criada.

dbglufke:SCOTT> select empno, ename, sal from emp2;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500
      7876 ADAMS            1100
      7900 JAMES             999
      7902 FORD             3000
      7934 MILLER           1000
       124 aaa             28774

16 linhas selecionadas.
É claro que eu teria que fazer mais duas triggers, uma pra UPDATE e outra pra DELETE... Mas basicamente é esse o caminho das pedras!
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Deixo um link muito bom do guru Tom Kyte... (em inglês)
http://asktom.oracle.com/~tkyte/Mutate/index.html

Selecionar tudo

Avoiding Mutating Tables
Ok, so you'vê just recieved the error:

ORA-04091: table XXXX is mutating, trigger/function may not see it

and you want to get around that. This short article will describe and demonstrate the various methods of getting around the mutating table error.

If you are interested in why you are getting it and in what cases you will get it, please see the Oracle Server Application Developers Guide (click here to read it right now -- this link is to technet.oracle.com.  You need a password to access this site but you can get one right away for free).

Avoiding the mutating table error is fairly easy.  We must defer processing against the mutating or constrainng table until an AFTER trigger.  We will consider two cases:
 

    * Hitting the ORA-4091 in an INSERT trigger or an UPDATE trigger where you only need access to the :new values
    * Hitting the ORA-4091 in a DELETE trigger or an UPDATE trigger where you need to access the :old values

Case 1 - you only need to access the :new values
This case is the simplest.  What we will do is capture the ROWIDS of the inserted or udpated rows.  We can then use these ROWIDS in an AFTER trigger to query up the affected rows.

It always takes 3 triggers to work around the mutating table error.  They are:
 

    * A before trigger to set the package state to a known, consistent state
    * An after, row level trigger to capture each rows changes
    * An after trigger to actually process the change.

As an example -- to show how to do this, we will attempt to answer the following question:
 

    I have a table containing a key/status/effective date combination.  When status
    changes, the values are propagated by trigger to a log table recording the
    status history.  When no RI constraint is in place everything works fine.

    When an RI trigger enforces a parent-child relationship, the status change
    logging trigger fails because the parent table is mutating.  Propagating the
    values to the log table implicitly generates a lookup back to the parent table
    to ensure the RI constraint is satisfied.

    I do not want to drop the RI constraint.  I realize that the status is
    denormalized.  I want it that way.  What is a good way to maintain the log?

Here is the implementation:

SQL> create table parent
  2  ( theKey        int primary key,
  3    status        varchar2(1),
  4    effDate       date
  5  )
  6  /
Table created.

SQL> create table log_table
  2  (       theKey  int references parent(theKey),
  3          status  varchar2(1),
  4          effDate date
  5  )
  6  /
Table created.

SQL> REM this package is used to maintain our state.  We will save the rowids of newly
SQL> REM inserted / updated rows in this package.  We declare 2 arrays -- one will
SQL> REM hold our new rows rowids (newRows).  The other is used to reset this array,
SQL> REM it is an 'empty' array

SQL> create or replace package state_pkg
  2  as
  3          type ridArray is table of rowid index by binary_integer;
  4
  4          newRows ridArray;
  5          empty   ridArray;
  6  end;
  7  /
Package created.

SQL> REM We must set the state of the above package to some known, consistent state
SQL> REM before we being processing the row triggers.  This trigger is mandatory,
SQL> REM we *cannot* rely on the AFTER trigger to reset the package state.  This
SQL> REM is because during a multi-row insert or update, the ROW trigger may fire
SQL> REM but the AFTER tirgger does not have to fire -- if the second row in an update
SQL> REM fails due to some constraint error -- the row trigger will have fired 2 times
SQL> REM but the AFTER trigger (which we relied on to reset the package) will never fire.
SQL> REM That would leave 2 erroneous rowids in the newRows array for the next insert/update
SQL> REM to see.   Therefore, before the insert / update takes place, we 'reset'

SQL> create or replace trigger parent_bi
  2  before insert or update on parent
  3  begin
  4          state_pkg.newRows := state_pkg.empty;
  5  end;
  6  /
Trigger created.

SQL> REM This trigger simply captures the rowid of the affected row and
SQL> REM saves it in the newRows array.

SQL> create or replace trigger parent_aifer
  2  after insert or update of status on parent for each row
  3  begin
  4          state_pkg.newRows( state_pkg.newRows.count+1 ) := :new.rowid;
  5  end;
  6  /
Trigger created.

SQL> REM this trigger processes the new rows.  We simply loop over the newRows
SQL> REM array processing each newly inserted/modified row in turn.

SQL> create or replace trigger parent_ai
  2  after insert or update of status on parent
  3  begin
  4          for i in 1 .. state_pkg.newRows.count loop
  5                  insert into log_table
  6                  select theKey, status, effDate
  7                    from parent where rowid = state_pkg.newRows(i);
  8          end loop;
  9  end;
 10  /
Trigger created.

SQL> REM this demonstrates that we can process single and multi-row inserts/updates
SQL> REM without failure (and can do it correctly)

SQL> insert into parent values ( 1, 'A', sysdate-5 );
1 row created.

SQL> insert into parent values ( 2, 'B', sysdate-4 );
1 row created.

SQL> insert into parent values ( 3, 'C', sysdate-3 );
1 row created.

SQL> insert into parent select theKey+6, status, effDate+1 from parent;
3 rows created.

SQL> select * from log_table;

    THEKEY S EFFDATE
---------- - ---------
         1 A 04-AUG-99
         2 B 05-AUG-99
         3 C 06-AUG-99
         7 A 05-AUG-99
         8 B 06-AUG-99
         9 C 07-AUG-99

6 rows selected.

SQL> update parent set status = chr( ascii(status)+1 ), effDate = sysdate;
6 rows updated.

SQL> select * from log_table;

    THEKEY S EFFDATE
---------- - ---------
         1 A 04-AUG-99
         2 B 05-AUG-99
         3 C 06-AUG-99
         7 A 05-AUG-99
         8 B 06-AUG-99
         9 C 07-AUG-99
         1 B 09-AUG-99
         2 C 09-AUG-99
         3 D 09-AUG-99
         7 B 09-AUG-99
         8 C 09-AUG-99
         9 D 09-AUG-99

12 rows selected.
 
Case 2 - you need to access the :old values
This one is a little more involved but the concept is the same.  We'll save the actual OLD values in an array (as opposed to just the rowids of the new rows).  Using tables of records this is fairly straightforward.  Lets say we wanted to implement a flag delete of data -- that is, instead of actually deleting the record, you would like to set a date field to SYSDATE and keep the record in the table (but hide it from queries).  We need to 'undo' the delete.

In Oracle8.0 and up, we could use "INSTEAD OF" triggers on a view to do this, but in 7.3 the implementation would look like this:
 

SQL> REM this is the table we will be flag deleting from.
SQL> REM No one will ever access this table directly, rather,
SQL> REM they will perform all insert/update/delete/selects against
SQL> REM a view on this table..

SQL> create table delete_demo ( a            int,
  2                             b            date,
  3                             c            varchar2(10),
  4                             hidden_date  date default to_date( '01-01-0001', 'DD-MM-YYYY' ),
  5                             primary key(a,hidden_date) )
  6  /
Table created.

SQL> REM this is our view.  All DML will take place on the view, the table
SQL> REM will not be touched.

SQL> create or replace view delete_demo_view as
  2  select a, b, c from delete_demo where hidden_date = to_date( '01-01-0001', 'DD-MM-YYYY' )
  3  /
View created.

SQL> grant all on delete_demo_view to public
  2  /
Grant succeeded.

SQL> REM here is the state package again.  This time the array is of
SQL> REM TABLE%ROWTYPE -- not just a rowid

SQL> create or replace package delete_demo_pkg
  2  as
  3      type array is table of delete_demo%rowtype index by binary_integer;
  4
  4      oldvals    array;
  5      empty    array;
  6  end;
  7  /
Package created.

SQL> REM the reset trigger...

SQL> create or replace trigger delete_demo_bd
  2  before delete on delete_demo
  3  begin
  4      delete_demo_pkg.oldvals := delete_demo_pkg.empty;
  5  end;
  6  /
Trigger created.

SQL> REM Here, instead of capturing the rowid, we must capture the before image
SQL> REM of the row.
SQL> REM We cannot really undo the delete here, we are just capturing the deleted
SQL> REM data

SQL> create or replace trigger delete_demo_bdfer
  2  before delete on delete_demo
  3  for each row
  4  declare
  5      i    number default delete_demo_pkg.oldvals.count+1;
  6  begin
  7      delete_demo_pkg.oldvals(i).a := :old.a;
  8      delete_demo_pkg.oldvals(i).b := :old.b;
  9      delete_demo_pkg.oldvals(i).c := :old.c;
 10  end;
 11  /
Trigger created.

SQL> REM Now, we can put the deleted data back into the table.  We put SYSDATE
SQL> REM in as the hidden_date field -- that shows us when the record was deleted.

SQL> create or replace trigger delete_demo_ad
  2  after delete on delete_demo
  3  begin
  4      for i in 1 .. delete_demo_pkg.oldvals.count loop
  5          insert into delete_demo ( a, b, c, hidden_date )
  6          values
  7          ( delete_demo_pkg.oldvals(i).a, delete_demo_pkg.oldvals(i).b,
  8            delete_demo_pkg.oldvals(i).c, sysdate );
  9      end loop;
 10  end;
 11  /
Trigger created.

SQL> REM Now, to show it at work...
SQL> insert into delete_demo_view values ( 1, sysdate, 'Hello' );
1 row created.

SQL> insert into delete_demo_view values ( 2, sysdate, 'Goodbye' );
1 row created.

SQL> select * from delete_demo_view;

         A B         C
---------- --------- ----------
         1 09-AUG-99 Hello
         2 09-AUG-99 Goodbye

SQL> delete from delete_demo_view;
2 rows deleted.

SQL> select * from delete_demo_view;
no rows selected

SQL> select * from delete_demo;

         A B         C          HIDDEN_DA
---------- --------- ---------- ---------
         1 09-AUG-99 Hello      09-AUG-99
         2 09-AUG-99 Goodbye    09-AUG-99 


OUTRA FORMA:
http://glufke.net/oracle/viewtopic.php?t=5125
Editado pela última vez por dr_gori em Qui, 27 Ago 2009 12:45 pm, em um total de 2 vezes.
HenriqueMachado
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 48
Registrado em: Seg, 29 Mai 2006 1:24 pm
Localização: Blumenau - SC
Abraços,
Henrique Machado Muller

Outra dica interecante para evitar o erro é seguir a dica do site:
http://www.oracle-base.com/articles/9i/ ... ptions.php

Nesta dica você não usa trigger INSTEAD-OF e sim usa types record com package e trigger de statement-level.

Espero que gostem
fsilveira
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Mensagens: 2
Registrado em: Qui, 23 Ago 2007 1:29 pm
Localização: Porto Alegre - RS

Opa,

Estou tentando efetuar um update atraves de um trigger mas da o erro de tabela mutante pois um dos updates é na propia tabela, pois existe um 'interesse' pai para os interesses como pode ser visto no codigo abaixo:

Selecionar tudo

CREATE TABLE INTERESSE (
  interesseid INTEGER NOT NULL,
  pai INTEGER NULL,
  descricao VARCHAR(60) NOT NULL,
  PRIMARY KEY(interesseid)
);

ALTER TABLE INTERESSE add
CONSTRAINT fk_interesse
FOREIGN KEY(pai)
REFERENCES INTERESSE(interesseid)
ON DELETE CASCADE;
Existem ainda mais duas tabelas que precisam ter seus interesses atualizados. Para estas tabelas eu estou executano uma trigger de update normal:

Selecionar tudo

CREATE OR REPLACE TRIGGER interesse
AFTER UPDATE OF interesseid ON interesse
FOR EACH ROW
BEGIN

UPDATE perfil_usuario
SET interesse_interesseid = :new.interesseid
WHERE interesse_interesseid = :old.interesseid;

UPDATE perfil_campanha
SET interesse_interesseid = :new.interesseid
WHERE interesse_interesseid = :old.interesseid;

END;
Mas para atualizar o atributo 'pai' da tabela interesse eu estou usando o que foi indicado:

Selecionar tudo

CREATE OR REPLACE TRIGGER interesse2
BEFORE UPDATE OF interesseid ON interesse
FOR EACH ROW
declare
Pragma Autonomous_Transaction;
nSequencia number := 0;
BEGIN

Select Max(interesseid) + 1
Into nSequencia
From interesse
Where pai = :New.interesseid;
:New.pai := nSequencia;

END;
Não aparce mais o erro de tabela mutante mas aparece error ORA-02292: restrição de integridade (SHOPPING4.FK_INTERESSE) violada - registro filho localizado. Alguém sabe o que esta errado?

Obrigado
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

To achando que você não deve fazer tudo isso dentro da trigger.
Veja só: sua tabela tem auto-relacionamento. Isso significa que podem ter registros filhos, netos, etc..

Talvez o ideal nesse caso é fazer uma rotina que faz isso OU usar exclusão lógica. (com um campo data_exclusão).

Só sei que o autonomous transaction não DEVE SER USADO! Se você leu o tópico você vai saber porque.
fsilveira
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Mensagens: 2
Registrado em: Qui, 23 Ago 2007 1:29 pm
Localização: Porto Alegre - RS

Não entendi como a exclusão lógica pode me ajudar. E poderia me explicar elhor sobre essa rotina?

Obrigado
SPECIALIZED
Rank: Programador Pleno
Rank: Programador Pleno
Mensagens: 40
Registrado em: Ter, 02 Out 2007 4:09 pm
Localização: São Paulo - SP

E no meu caso, qual seria a forma pra não dar mutanting
http://glufke.net/oracle/viewtopic.php?t=3671

Abraços :)
marujinho
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Mensagens: 1
Registrado em: Seg, 13 Out 2008 12:08 am
Localização: Rio de Janeiro

Olá pessoal,
estava tendo esse problema de mutating e apliquei a solução do Tom Kyte (a primeira do link que passaram), com isso eu recupero o rowid da linha, mas mesmo assim, continua dando erro de mutating, será que alguém pode dar uam ajuda nisso?
Seguem os scripts:

Selecionar tudo

CREATE OR REPLACE PACKAGE pk_state
AS
   TYPE ridArray IS TABLE OF ROWID INDEX BY BINARY_INTEGER;

   newRows ridArray;
   empty   ridArray;
END;
/

CREATE OR REPLACE TRIGGER TG_GC_CBO_TBL_EMPTY
BEFORE INSERT OR UPDATE 
ON PS_GC_CBO_TBL
BEGIN 
   pk_state.newRows := pk_state.empty;
END;
/ 

CREATE OR REPLACE TRIGGER TG_GC_CBO_TBL_NEWROW
BEFORE INSERT OR UPDATE 
ON PS_GC_CBO_TBL
FOR EACH ROW
BEGIN
   pk_state.newRows(pk_state.newRows.COUNT + 1) := :NEW.ROWID;
END; 
/
Parte do código da trigger:

Selecionar tudo

BEGIN
                    FOR i IN 1..pk_state.newRows.COUNT 
                    LOOP
                       DELETE
                         FROM ps_gc_cbo_tbl c
                        WHERE c.ROWID = pk_state.newRows(i);
                    END LOOP;    
                 EXCEPTION
                    WHEN OTHERS THEN
                       dbms_output.put_line(passo || SYSDATE || SQLERRM);
                 END;
Como disse, ele chega a recuperar o rowid, mas não executa o comando.
debby_acacio
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 51
Registrado em: Ter, 22 Jan 2008 11:38 am
Localização: Salvador - BA
Contato:

Oi Desculpa mas não tinha atentando ao primeito tópico do forum, eço descuilpas a todos... mas vamos lá!!!!!

Já li os tópicos sobre trigger com esse erro ORA-04091.
Como vi alguns exemplos aqui em outras tableas.

1) Fiz uma package ...
2) Fi uma nova trigger after, que faz o select na tabela em questão...

3) declarei os dados dados na trigger before delete , each- row

Mas não sei se fiz alguma cosa errada e continua dando o mesmo erro.
Vou tentar resumir...

Tenho uma tabela de titulos, com duas parcelas...
A filha desta é uma de movimento com os detalhes de cada parcela...até ai tudo bem.

Quando tento excuir uma parcela...
dá os seguintes erros

Selecionar tudo

ORA-04091: table SFT.SFT_VENCIMENTO_TITULO_CLIENTE is mutating, trigger/function may not see it 
ORA-06512: at "SFT.TRGA_S_SFT_MOVTO_VENC_TIT_CLI", line 20 
ORA-06512: at "SFT.TRGA_S_SFT_MOVTO_VENC_TIT_CLI", line 74 
ORA-04088: error during execution of trigger 'SFT.TRGA_S_SFT_MOVTO_VENC_TIT_CLI' 
ORA-06512: at "SFT.TRGB_SFT_VENCTO_TITULO_CLIENTE", line 325 
ORA-04088: error during execution of trigger 'SFT.TRGB_SFT_VENCTO_TITULO_CLIENTE' 
Alguém pode me ajudar ?
Grata
Debby
diegolenhardt
Moderador
Moderador
Mensagens: 1177
Registrado em: Qui, 15 Out 2009 10:28 am
Localização: Recife

você em algum momento está referenciando a propria tabela que disparou a trigger, para resolver faca uma VIEW, e crie uma trigger instead of na VIEW
debby_acacio
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 51
Registrado em: Ter, 22 Jan 2008 11:38 am
Localização: Salvador - BA
Contato:

Oi

Seguinte : Na tabela VENCIMENTO_TITULO_CLIENTE...
Tem uma trigger before, insert, or update ou delete for each-row

Onde faço um delete em MOVTO_VENCTO_TITULO_CLIENTE que é filha da mesma... sendo que : na triger before insert, or update ou delete for each-row faço um select na mãe.. para pegar o status da parcela...
Dai fiz uma package, e criei uma nova trigger after em MOVTO_VENCTO_TITULO_CLIENTE para corrigir... mas não está dando !!!

Mas ainda assim conitnua dando o ORA-04091

Deu para entender ?

Dá para explicar melhor esse lance com a view ??

Grata
Debby :)
diegolenhardt
Moderador
Moderador
Mensagens: 1177
Registrado em: Qui, 15 Out 2009 10:28 am
Localização: Recife

Veja aí a parte onde fala sobre INSTEAD OF,

é basicamente uma trigger em uma VIEW, essa VIEW faz o select na tabela em alteracao,

http://www.linhadecodigo.com.br/Artigo.aspx?id=322
debby_acacio
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 51
Registrado em: Ter, 22 Jan 2008 11:38 am
Localização: Salvador - BA
Contato:

OI vou mostar aqui passo a passo do que estou fazendo ok ?

1) quero apagar parcelas em TABELA_1 que aciona incialmente a trigger :

Selecionar tudo

create or replace trigger trgb_TABELA_1
  before insert or update or delete
  on TABELA_1
for each row
  --
declare
  --
  
Begin
  If inserting then
    etc....
        etc...
            etc....
  Elsif updating then
     etc.... 
         etc....
         
            etc....
                      
  Elsif  deleting then
    -- N?o deixara excluir se tiver movimentos lançados que sejam diferente do movimento de entrada de titulo.
    vmsg_titulo := ' Empresa '||to_char(:old.codigo_empresa)||' Serie '||:old.serie||' Titulo '||to_char(:old.numero_titulo)||' Parcela '||to_char(:new.numero_parcela);
    --
     
  	for x in (select codigo_movimento
  						from TABELA_2 m
  						where m.numero_titulo  = :old.numero_titulo
  						and   m.serie          = :old.serie
  						and   m.codigo_empresa = :old.codigo_empresa
  						and   m.numero_parcela = :old.numero_parcela
  						and   m.tipo_movimento  <> 'ENTRA')
    loop
        raise_application_error(-20019,'Msg ! Parcela n?o podera ser excluida. Existem movimentos associados.'||vmsg_titulo);
    end loop;
    --

    delete TABELA_2 m
    where  m.numero_titulo  = :old.numero_titulo
    and    m.serie          = :old.serie
    and    m.codigo_empresa = :old.codigo_empresa
    and    m.numero_parcela = :old.numero_parcela;
    --
    --
  end if;
  -- 
end;
Até aqui tudo bem... como existe movimento nas parcelas que quero apagar em TABELA_2 aciona a seguinte trigger :

Selecionar tudo

create or replace trigger trgb_TABELA_2
  before insert or update or delete on TABELA_2
  for each row
declare
  --
  v1 number;
  
  --

  vpos                    number;
  --
begin

  --
  --
  if inserting or updating then
    --

    if :new.data_contabil is null then
      :new.data_contabil := :new.data_movimento;
    end if;
    --
    for x in (select 1
              from  TABELA_FULANA tmc
              where tmc.codigo_movimento = nvl(:old.codigo_movimento,:new.codigo_movimento)
              and   tmc.tipo_movimento = 'ENTRA')
    loop
      -- Quando o movimento for fo tipo ENTRADA tanto MANUAL quanto POR NOTA FISCAL não ira executar os procedimentos dessa trigger.
      return;
    end loop;
   --
End if;
  --
  -- criei para acionar a package  sft_pck_atu_movto_venc_tit_cli
  
  vpos := nvl(sft_pck_atu_movto_venc_tit_cli.titulos.last, 0) + 1;

                                                                  
    if deleting then
     --
     --

          sft_pck_atu_movto_venc_tit_cli.titulos(vpos).serie := :old.serie ;
          sft_pck_atu_movto_venc_tit_cli.titulos(vpos).numero_titulo := :old.numero_titulo ;
          sft_pck_atu_movto_venc_tit_cli.titulos(vpos).codigo_empresa := :old.codigo_empresa ;
          sft_pck_atu_movto_venc_tit_cli.titulos(vpos).numero_parcela := :old.numero_parcela ;
          sft_pck_atu_movto_venc_tit_cli.titulos(vpos).codigo_movimento := :old.codigo_movimento ;
          sft_pck_atu_movto_venc_tit_cli.titulos(vpos).tipo_movimento := :old.tipo_movimento ;
          sft_pck_atu_movto_venc_tit_cli.titulos(vpos).valor := :old.valor ;
          sft_pck_atu_movto_venc_tit_cli.titulos(vpos).sequencia_instrucao_titulo_bco  := :old.sequencia_instrucao_titulo_bco ;
          sft_pck_atu_movto_venc_tit_cli.titulos(vpos).sequencia_movimento := :old.sequencia_movimento;
          
  end if;
       
end;
Até aqui também tranquilo... segue o script de criação da package

Selecionar tudo


create or replace package sft_pck_atu_movto_venc_tit_cli is
--    Esta Package cria uma pl-table que sera usada na trigger
--    trga_s_TABELA_2 para testar os valores do
--    do titulo cliente
--    --
--    Local Chamada : trga_s_TABELA_2 do sistema financeiro.
--    Criada em     : 11/2009.
--    Programador   : Débora Acácio
--    Analista      : XXXXXXXXXXXXX

type titulo_record is record
    ( serie                                   TABELA_2.serie%type
    , numero_titulo                    TABELA_2.numero_titulo%type
    , codigo_empresa              TABELA_2.codigo_empresa%type
    , numero_parcela               TABELA_2.numero_parcela%type
    ,codigo_movimento            TABELA_2.codigo_movimento%type
    ,tipo_movimento                  TABELA_2.tipo_movimento%type 
    ,valor                                       TABELA_2.valor%type    
    ,sequencia_instrucao_titulo_bco  TABELA_2.sequencia_instrucao_titulo_bco %type 
    ,sequencia_movimento  TABELA_2.sequencia_movimento%type );
   --
  type titulo_table is table
    of titulo_record
    index by binary_integer;
  --
  titulos titulo_table;
  --
End sft_pck_atu_movto_venc_tit_cli;

Debugando todas elas percebi que até esse momento é tudo tranquilo...
o problema acontece nessa trigger AFTER que criei ....segue o código:

Selecionar tudo

create or replace trigger trga_s_TABELA_2
  after  delete on TABELA_2

--  Autor         : Débora Acácio em 23/11/2009
-- Chamado : 7581

  --
declare

  v1 number;

  --
  vserie                                              TABELA_2.serie%type;
  vnumero_titulo                              TABELA_2.numero_titulo%type;
  vcodigo_empresa                        TABELA_2.codigo_empresa%type;
  vnumero_parcela                         TABELA_2.numero_parcela%type;
  vcodigo_movimento                    TABELA_2.codigo_movimento%type;
  vtipo_movimento                          TABELA_2.tipo_movimento%type;
  vvalor                                               TABELA_2.valor%type;
   vsequencia_titulo_bco               TABELA_2.sequencia_instrucao_titulo_bco%type;
   vsequencia_movimento             TABELA_2.sequencia_movimento%type;
  --
    cursor c_parcela(pnumero_titulo in varchar2
                                    ,pserie          in varchar2
                                    ,pcodigo_empresa  in number
                                     ,pnumero_parcela  in number ) is
    select tc.codigo_cliente
          ,tc.data_movimento
          ,vt.status_parcela
          ,vt.perda
          ,vt.codigo_banco
          ,vt.sequencia_parametro_banco
          ,tc.codigo_empresa_matriz
    from   TABELA_CLIENTE tc
          ,TABELA_1
    where  vt.numero_titulo  = pnumero_titulo
    and    vt.serie          = pserie
    and    vt.codigo_empresa = pcodigo_empresa
    and    vt.numero_parcela = pnumero_parcela
    and    tc.numero_titulo  = vt.numero_titulo
    and    tc.serie          = vt.serie
    and    tc.codigo_empresa = vt.codigo_empresa;
  --
  vparcela c_parcela%rowtype;

  vsequencia  pls_integer;
  --
  --
vmsg_titulo varchar2(2000);

begin
  --

  if sft_pck_atu_movto_venc_tit_cli.titulos.first is null then
    --
    return;
    --
  end if;
  --
  for vpos in sft_pck_atu_movto_venc_tit_cli.titulos.first..
              sft_pck_atu_movto_venc_tit_cli.titulos.last
  loop
    --


    vserie              := sft_pck_atu_movto_venc_tit_cli.titulos(vpos).serie;
    vnumero_titulo   := sft_pck_atu_movto_venc_tit_cli.titulos(vpos).numero_titulo;
    vcodigo_empresa    := sft_pck_atu_movto_venc_tit_cli.titulos(vpos).codigo_empresa;
    vnumero_parcela      := sft_pck_atu_movto_venc_tit_cli.titulos(vpos).numero_parcela;
    vcodigo_movimento := sft_pck_atu_movto_venc_tit_cli.titulos(vpos).codigo_movimento;
    vtipo_movimento      := sft_pck_atu_movto_venc_tit_cli.titulos(vpos).tipo_movimento;
    vvalor                          := sft_pck_atu_movto_venc_tit_cli.titulos(vpos).valor;
    vsequencia_titulo_bco := sft_pck_atu_movto_venc_tit_cli.titulos(vpos).sequencia_instrucao_titulo_bco ;
    vsequencia_movimento := sft_pck_atu_movto_venc_tit_cli.titulos(vpos).sequencia_movimento;
    --
     v1 := 6000 ;
     vmsg_titulo := 'Série '||vserie||'- titulo = '||vnumero_titulo||' parcela = '||vnumero_parcela||' empresa = '||vcodigo_empresa ;
     
    raise_application_error(-20002,v1||' - dentro do loop antes  do open  '|| vmsg_titulo );

      open c_parcela(vnumero_titulo,vserie,vcodigo_empresa,vnumero_parcela);
      fetch c_parcela into vparcela;
      close c_parcela;


      vsequencia := sft_pck_instrucao_banco.fnc_inserir_instrucao_banco
                                              (pcodigo_banco               => vparcela.codigo_banco
                                              ,psequencia_parametro_banco  => vparcela.sequencia_parametro_banco
                                              ,pnumero_titulo              => vnumero_titulo
                                              ,pserie                      => vserie
                                              ,pcodigo_empresa             => vcodigo_empresa
                                              ,pnumero_parcela             => vnumero_parcela
                                              ,pcodigo_movimento_cobranca  => vcodigo_movimento
                                              ,ptipo_movimento_cobranca    => vtipo_movimento
                                              ,pvalor_movimento            => vvalor*-1
                                              ,psequencia_instrucao_titulo => vsequencia_titulo_bco
                                              ,pestorno                    => 'N'
                                              ,ptipo_operacao              => 'E');


    --
  end loop;
  --
  sft_pck_atu_movto_venc_tit_cli.titulos.delete;
  --
end;
O erro acontece justamente no momento do open do cursor c_parcela .... onde faço um select em TABELA_1

Deu para entender ????
Alguém pode por favor, me dizer onde está o meu "erro "... vi aqui no forum que esse exemplo funcionou para alguém... mas não me recordo em que tópico foi !!!!

Um bom dia a todos
Debby
diegolenhardt
Moderador
Moderador
Mensagens: 1177
Registrado em: Qui, 15 Out 2009 10:28 am
Localização: Recife

O seu cursor faz select na tabela que originou tudo isso,

ou seja, vai dar erro mesmo, você não pode referenciar em momento algum a tabela da trigger disparada, pois a outra trigger disparou por causa da primeira, então em lugar algum você pode referenciar a tabela dessa trigger.
debby_acacio
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 51
Registrado em: Ter, 22 Jan 2008 11:38 am
Localização: Salvador - BA
Contato:

Obrigada...

Preciso de duas informções da TABELA_1. Para validar a deleção da parcela...

Eu posso então, passar o valor desses dois campos na mesma package ?
Dai não precisaria do select .

Grata
Debby
diegolenhardt
Moderador
Moderador
Mensagens: 1177
Registrado em: Qui, 15 Out 2009 10:28 am
Localização: Recife

Isso, pode passar como parametro os campos :OLD.campo etc

=)
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

Você pode ter problemas de concorrência se mais de um camarada tentar executar a transação ao mesmo tempo, pois não há garantias que o valor que você vai ler da package é o que você gravou na transação corrente...
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

Você teria que criar na package um tabela em memória (index-by table, por exemplo) e gravar as linhas lá por rowid ou chave primária de forma a identificar elas na transação atual e recuperar o valor :old.

Tem no forum vários exemplos de soluções de mutating trigger, seja com package ou tabela auxiliar. Eu sugeriria "limpar" sua lógica para testar e entender o conceito e apenas incrementar com a parafernália toda depois que você compreender como resolver o problema.
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

Notei que o link lá referenciado está quebrado, leia a explicação do porquê não se pode fazer esse tipo de leitura na tabela aqui:
http://www.oracle.com/technology/oramag ... sktom.html

E veja como corrigir aqui:
http://asktom.oracle.com/pls/asktom/f?p ... 0416059674
debby_acacio
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 51
Registrado em: Ter, 22 Jan 2008 11:38 am
Localização: Salvador - BA
Contato:

Oi gentem
Conseguiiiiiiii

Criei duas variáveis na Package e alimento no before da TABELA_1....

Obrigada a todos

Debby :)
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

É como falei anteriormente: se dois usuários executarem sua transação ao mesmo tempo, vai dar pau, variável de package não tem isolamento de leitura ou gravação.

Funcionar não quer dizer que está correto.

Abraço,
Francisco.
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

Você testou atualizar várias linhas de uma vez só? A package vai sobrescrever os valores de uma linha sobre a outra.
Thyago Oliveira
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Mensagens: 1
Registrado em: Qua, 21 Ago 2013 10:45 am

dr_gori estou acompanhando a discussão do tópico e me surgiu uma ideia. E se eu declarar o Pragma Autonomous_Transaction na trigger para criar uma transação separada e no final der o "commit;" ?
Responder
  • Informação
  • Quem está online

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