Avoiding the Mutating on 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
  

Poston Fri, 02 Jul 2004 1:31 pm

I found this tip below about avoid Mutating in this Oracle site: http://www.linhadecodigo.com.br/dicas.a ... 991&sub=57 does it really?

Code: Select all
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;
tfg
Location: Novo Hamburgo - RS

Poston Fri, 02 Jul 2004 1:33 pm

ARE SADLY WRONG.


We will apply and see why this tip is stuck!
1. We create the table used:
Code: Select all
create table tab_enderecos 
( cliente_id    number(10)
, endereco_id   number(10)
, endereco      varchar2(100)
);
2. On its face, I'vê seen that will give error in trigger for 2 reasons: (a). Syntax error in the Declaration of the variable. Should be:
Code: Select all
  nSequencia number:= 0;
(b). There's no exception. Will give error if the table is empty or is the first record inserted in the CLIENTE_ID..

3. So, let's enter now the client 1 (to avoid the error).
Code: Select all
SQL> insert into tab_enderecos values (1, 1, 'meu endereço');

1 row created.
SQL> COMMIT;
4. Now, apply the trigger-correcting the error in the statement that I quoted in item 3.

5. OK! Ready to test! The trigger promise to put the automatic ENDERECO_ID!!! Is what you would see:
Code: Select all
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.
Why? It worked? YES, because the first was comitado! If you run another insert without having comitado the previous will give error. That's because the Autonomous Transaction works in another session, i.e., can't see what you just entered. So this tip is fully pierced. See what happens if I insert a row without committing the previous:
Code: Select all
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.
DOUBLED the 2 ID!!!!! ERROR!!!

Care must be taken when using this type of transaction, because you can be making a great nonsense and kicking your information base. (as in the example cited in " tip " above)
dr_gori
Location: Portland, OR USA

Thomas F. G

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

Poston Fri, 16 Jul 2004 7:33 pm

Now Yes ... a way that WORKS to prevent mutating.
Let's use INSTEAD-OF triggers!!!

The following example creates a trigger INSTEAD OF to the VIEW emp2-> that is a copy of the emp.
Code: Select all
create or replace view emp2 as
select * from emp
Now let's create a trigger that makes a SELECT in EMP. (that is, something that normally would give mutating.)

Code: Select all
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;
Let's make a test:
Code: Select all
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.
of course I would have to make two triggers, one for UPDATE and another to DELETE ... But basically this is the way of the stones.
dr_gori
Location: Portland, OR USA

Thomas F. G

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

Poston Tue, 14 Mar 2006 5:05 pm

I leave a link very nice of the guru Tom Kyte ... (in English) http://asktom.oracle.com/~tkyte/Mutate/index.html
Code: Select all
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
OTHERWISE: viewtopic.php?t=5125
Last edited by dr_gori on Thu, 27 Aug 2009 12:45 pm, edited 2 times in total.
dr_gori
Location: Portland, OR USA

Thomas F. G

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

Poston Tue, 26 Jun 2007 10:09 am

Another tip interecante to avoid the error is to follow the hint from the website: http://www.oracle-base.com/articles/9i/ ... ptions.php in this tip you don't use INSTEAD-OF trigger and Yes uses record types with package and statement-level trigger.

I hope you enjoy
HenriqueMachado
Location: Blumenau - SC

Abraços,
Henrique Machado Muller

Poston Thu, 23 Aug 2007 1:40 pm

Oops, I'm trying to make an update via a trigger but from the table error mutant because one of the updates is in own table, as there is a ' interest ' father to the interests as can be seen in the following code:
Code: Select all
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;
there are still two more tables that need to have their interests up to date. For these tables, I'm executano a normal update trigger:
Code: Select all
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;
But to update the attribute ' father ' of interest table I'm using what was indicated:
Code: Select all
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;
no more aparce the table error mutant but appears error ORA-02292: (SHOPPING4 integrity constraint.FK_INTERESSE) violated-child record found. Does anyone know what's wrong?

Thank you
fsilveira
Location: Porto Alegre - RS

Poston Thu, 23 Aug 2007 4:56 pm

To think that you should not do all this within the trigger.
Get this: your table has auto-relacionamento. This means that they can have children, grandchildren, records, etc..

Perhaps the ideal in this case is to do a routine that makes this or use logical exclusion. (with a data_exclusão).

All I know is that the autonomous transaction SHOULD NOT BE USED! If you have read the topic you will know why.
dr_gori
Location: Portland, OR USA

Thomas F. G

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

Poston Mon, 27 Aug 2007 2:00 pm

I don't understand how the deletion logic can help me. And could you explain forensic surgeon on this routine?

Thank you
fsilveira
Location: Porto Alegre - RS

Poston Thu, 18 Sep 2008 2:40 pm

And in my case, what would be the way to not give mutanting viewtopic.php?t=3671:) Hugs
SPECIALIZED
Location: São Paulo - SP

Poston Fri, 21 Nov 2008 11:09 am

Hello guys, I was having this problem of mutating and applied the solution from Tom Kyte (the first link which passed), thus I get the rowid of the row, but even so, continues giving error of mutating, can anyone give a help on this?
Follow the scripts:
Code: Select all
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;
/
part of the trigger code:
Code: Select all
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;
like I said, he arrives to retrieve the rowid, but does not execute the command.
marujinho
Location: Rio de Janeiro

Poston Mon, 23 Nov 2009 2:32 pm

Hi Sorry but I wasn't paying attention to the first topic of the forum, eço descuilpas everyone ... but come on!!!!!

I'vê read the threads on trigger with this error ORA-04091.
As I saw some examples here in other table.

1) Did a package ...
2) Fi a new trigger after, which makes the select in the table in question.

3) declared the data in the trigger before delete, each-row but I don't know if I did anything wrong and still giving the same error.
I will try to summarize ...

I have a table of titles, with two installments.
This is a daughter of movement with the details of each parcel ... over there all right.

When I try to delete a portion ... it gives the following errors
Code: Select all
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'
can someone help me?
Grateful Debby
debby_acacio
Location: Salvador - BA

Poston Mon, 23 Nov 2009 4:01 pm

At some point you're referencing her own table that fired the trigger, to resolve a VIEW, and create an instead of trigger on the VIEW
diegolenhardt
Location: Recife

Poston Mon, 23 Nov 2009 4:12 pm

HI: in the table VENCIMENTO_TITULO_CLIENTE.
Has a trigger before, insert, or update, or delete statements for each-row where I do a delete on MOVTO_VENCTO_TITULO_CLIENTE that is a child of same ... being that: the triger before insert, or update, or delete statements for each-row do a select on mother ... to get the status of the parcel.
Dai did a package, and created a new trigger after in MOVTO_VENCTO_TITULO_CLIENTE to fix ... but it's not working!!!

But still giving conitnua ORA-04091 Gave to understand?

Can you elaborate on this thing with a view?

Grateful Debby:)
debby_acacio
Location: Salvador - BA

Poston Mon, 23 Nov 2009 4:20 pm

Check the part where he talks about INSTEAD OF, it's basically a trigger on a VIEW , this VIEW makes the select the table changes, http://www.linhadecodigo.com.br/Artigo.aspx?id=322
diegolenhardt
Location: Recife

Poston Wed, 25 Nov 2009 8:00 am

HI I will show here step by step of what I'm doing okay?

1) I delete plots in TABELA_1 that fires initially trigger:
Code: Select all
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;
so far, so good ... as there is movement on the plots that I want to delete TABELA_2 triggers the following trigger:
Code: Select all
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;
this far too quiet ... follows the script of the package creation
Code: Select all

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;

Debugging all of them realized that until that moment is all quiet ... the problem happens in this trigger AFTER I created.--follow the code :
Code: Select all
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;
the error happens precisely at the time of cursor open c_parcela ... where do a select on TABELA_1 Gave to understand????
Can someone please tell me where is my " " error ... I saw here in the forum that this example worked for somebody ... but I don't remember in which topic was!!!!

A good day all Debby
debby_acacio
Location: Salvador - BA

Poston Wed, 25 Nov 2009 9:12 am

Your cursor's select on table that originated it, i.e. will give error, you cannot reference a table trigger fired because the other trigger fired because of the first, so anywhere you can reference the table this trigger.
diegolenhardt
Location: Recife

Poston Wed, 25 Nov 2009 10:06 am

Thank You ...

I need two information of TABELA_1. To validate the deletion of the parcel ...

I can then pass the value of these two fields in the same package?
Then he wouldn't need a select.

Thanks Debby
debby_acacio
Location: Salvador - BA

Poston Wed, 25 Nov 2009 10:08 am

It can pass as a parameter the fields: OLD camp etc =)
diegolenhardt
Location: Recife

Poston Wed, 25 Nov 2009 12:09 pm

You can have competition concerns if more than a comrade attempting to perform the transaction at the same time, because there's no guarantee that the value that you will read of the package is that you recorded in the current transaction.
fsitja
Location: 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

Poston Wed, 25 Nov 2009 12:15 pm

You would have to create the package a memory table (index-by table, for example) and record the lines there by rowid or primary key to identify them in the current transaction and retrieve the value: old.

Has the forum several examples of solutions of mutating, trigger package or auxiliary table. I would suggest \"clean\" your logic to test and understand the concept and only increase with the whole paraphernalia after you understand how to solve the problem.
fsitja
Location: 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


  • See also
    Replies
    Views
    Last Post