Avoiding Mutating the 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
Post Reply
User avatar
tfg
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 83
Joined: Mon, 03 May 2004 3:38 pm
Location: Novo Hamburgo - RS

I found this tip below about avoiding mutating on this Oracle site: http://www.linhadecodigo.com.br/dicas.a ... 991&sub=57
Does it solve?

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;
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

is roundly wrong! \r\n

Let's apply and see why this tip is stuck!
1. Let's create the table used:

Select all

create table tab_enderecos   
( cliente_id    number(10) 
, endereco_id   number(10) 
, endereco      varchar2(100) 
);
2. From face, I have already seen that it will give a mistake in the trigger for 2 reasons:
(a). Syntax error in the variable statement. It should be:

Select all

  nSequencia number:= 0;
(b). There is no exception. It will give an error if the table is empty or the first register inserted from the client_id ..

3. So let's insert the client 1 (to avoid the error).

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 of the statement I mentioned in item 3.

5. OK! Ready to test! The trigger promises that will put the automatic address_id !!! This is what we will see:

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. 
Ué? It worked? Yes, because the first was committed! If you run another insert without having commanded the previous one, it will be error. This is because AutoNomous Transaction works in another session, that is, does not see what you just inserted. Therefore, this tip is fully stuck. See what happens if I insert a line without committing the previous:

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. 
Duplicated ID 2 !!!!! ERROR!!!

One must be very careful when using this type of transaction, because you may be making a great nonsense and detonating the information on your base. (as in the example quoted in the "tip" above)
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

Now yes ... a way that works to avoid mutating ...
Let's use Triggers instead-of !!!

The example below, creates a Trigger instead-of for the VIEW EMP2 -> which is a copy of EMP!

Select all

create or replace view emp2 as 
select * from emp
Now let's create a trigger that makes a Select in the EMP. (that is, something that would normally give mutating.)

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 take a test:

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 do two more triggers, one for update and another to delete ... but basically this is the path of the stones!
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

I leave a very good link from Guru Tom Kyte ... (in English) http://asktom.oracle.com/~tkyte/Mutate/index.html

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 

other form: http://en.glufke.net/oracle/viewtopic.php?t=5125
Last edited by dr_gori on Thu, 27 Aug 2009 12:45 pm, edited 2 times in total.
HenriqueMachado
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 48
Joined: Mon, 29 May 2006 1:24 pm
Location: Blumenau - SC
Abraços,
Henrique Machado Muller

Another intemperative tip to avoid the error is to follow the tip of the site: http://www.oracle-base.com/articles/9i/ ... ptions.php
In this tip you do not use Trigger Instead-of, but you use Types Record with Package and Statement-Level Trigger.

I hope you like
fsilveira
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 2
Joined: Thu, 23 Aug 2007 1:29 pm
Location: Porto Alegre - RS

Opa,

I am trying to perform an update through a trigger but the mutant table error because one of the updates is in propose table, because there is a 'interest' parent for interests as can be Seen on the code below:

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 even more two tables that need to have their interests updated. For these tables I am executing a normal update trigger:

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 'parent' attribute of the interest table I am using what was indicated :

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 longer appears the mutant table error but it appears error or-02292: integrity restriction (shopping4.fk_interesse) violated - Located child record. Does anyone know what's wrong?

Thanks
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

I think you should not do all this inside the trigger.
View: Your table has self-relationship. This means that you can have child records, grandchildren, etc.

maybe ideal in this case is to make a routine that does this or use logical deletion. (with a data_exclusion field).

I only know that Autonomous Transaction should not be used! If you read the topic you will know why.
fsilveira
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 2
Joined: Thu, 23 Aug 2007 1:29 pm
Location: Porto Alegre - RS

I did not understand how logical exclusion can help me. And could you explain Elhor about this routine?

Thanks
SPECIALIZED
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 40
Joined: Tue, 02 Oct 2007 4:09 pm
Location: São Paulo - SP

And in my case, what would be the way to not give mutanting http://en.glufke.net/oracle/viewtopic.php?t=3671
Hugs :)
marujinho
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 1
Joined: Mon, 13 Oct 2008 12:08 am
Location: Rio de Janeiro

Hello everyone,
was having this mutating problem and I applied the Tom Kyte solution (the first of the link that passed), with this I recover the rowid of the line, but even then, it continues to give mutating error, it will be Can anyone give it help?
follow the scripts:

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:

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; 
As I said, he gets recovering the rowid, but does not perform the command.
debby_acacio
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 51
Joined: Tue, 22 Jan 2008 11:38 am
Location: Salvador - BA
Contact:

Hi, sorry but I had no attentive to the prime topic from the forum, I'll find out everyone ... but come on !!!!!

I have read the topics about trigger with this error or-04091.
As I saw some examples here on other tablets.

1) I made a package ...
2) FI a new Trigger After, which does the Select in the table in question ...

] 3) I declared the data data at the Trigger Before delete, each-row

but I do not know if I did some misconduct and continues to give the same error.
I will try to summarize ...

I have a table of titles, with two installments ...
The daughter of this is a movement with the details of each parcel ... So far so good.

When I try to excuse a portion ...
gives the following errors

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 anyone help me?
Grata
Debby
diegolenhardt
Moderador
Moderador
Posts: 1177
Joined: Thu, 15 Oct 2009 10:28 am
Location: Recife

You at some point is referencing the own table that fired the trigger, to solve a view, and create a trigger instead of in the view
debby_acacio
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 51
Joined: Tue, 22 Jan 2008 11:38 am
Location: Salvador - BA
Contact:

Oi

Next: In the maturity table_title_client ...
has a Trigger Before, INSERT, OR UPDATE or DELETE for EACH-ROW
where I make a delete in moving_vencto_titulo_client that is the daughter of the same ... and: at the triger before insert, or update or delete for each-row I do a select in the mother .. to get the status of the portion ...
Dai I made a package, and I created a new trigger after in movto_vencto_titulo_client to correct ... but it is not giving !!!

But still Conitnua giving ORA-04091

Did you understand?

Can you explain this bid with View ??

Grata
Debby :)
diegolenhardt
Moderador
Moderador
Posts: 1177
Joined: Thu, 15 Oct 2009 10:28 am
Location: Recife

SEE there the part where you talk about instead of,

is basically a trigger in a view , this view does the Select in the table in alteration,
http://www.linhadecodigo.com.br/Artigo.aspx?id=322
debby_acacio
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 51
Joined: Tue, 22 Jan 2008 11:38 am
Location: Salvador - BA
Contact:

Hi, I'll take you here by step what I'm doing, okay?

1) I want to delete parcels in table_1 that activates the trigger initially:

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 in the installments that I want to delete in Table_2 triggers the following trigger:

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; 
Also still quiet ... follows the package creation script

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 I realized that until this moment is all quiet ...
The problem happens in this trigger after that I created .... Follow the 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 occurs precisely at the time of the Open of the Cursor C_CARCELA .... where I do a SELECT in table_1

Did you understand ????
Someone can please tell me where my "mistake" ... I saw here in the forum that this example worked for someone ... but I do not remember what topic it was !!!!

A good day to all
Debby
diegolenhardt
Moderador
Moderador
Posts: 1177
Joined: Thu, 15 Oct 2009 10:28 am
Location: Recife

Your cursor makes select in the table that originated all this,

ie it will give error even, you can not reference at any time the trigger table triggered, because the other trigger fired because From the first, then anywhere you can reference the table of this trigger.
debby_acacio
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 51
Joined: Tue, 22 Jan 2008 11:38 am
Location: Salvador - BA
Contact:

Thank you ...

I need two table informations. To validate the deletion of the portion ...

I can then pass the value of these two fields in the same package?
Dai would not need SELECT.

Grata
Debby
diegolenhardt
Moderador
Moderador
Posts: 1177
Joined: Thu, 15 Oct 2009 10:28 am
Location: Recife

This can pass as parameter the fields: OLD.CAMPO etc

=)
User avatar
fsitja
Rank: OraSauro
Rank: OraSauro
Posts: 611
Joined: Mon, 19 Jan 2009 4:29 pm
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

You can have competition problems if more than one comrade try to run the transaction at the same time as there are no guarantees that the value you will read from the package is what you recorded in the current transaction ...
User avatar
fsitja
Rank: OraSauro
Rank: OraSauro
Posts: 611
Joined: Mon, 19 Jan 2009 4:29 pm
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

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

In the forum several examples of mutating trigger solutions, whether with package or auxiliary table. I would suggest "cleaning" your logic to test and understand the concept and just increment with paraphernalia all after you understand how to solve the problem.
User avatar
fsitja
Rank: OraSauro
Rank: OraSauro
Posts: 611
Joined: Mon, 19 Jan 2009 4:29 pm
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

I noticed that the link there is broken, read the explanation of why you can not do this type of reading in the table here: http://www.oracle.com/technology/oramag ... sktom.html
and see how to correct here: http://asktom.oracle.com/pls/asktom/f?p ... 0416059674
debby_acacio
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 51
Joined: Tue, 22 Jan 2008 11:38 am
Location: Salvador - BA
Contact:

Hi gentem
achieved

I created two variables on the package and food in the before of table_1 ....

Thanks to all

Debby :)
User avatar
fsitja
Rank: OraSauro
Rank: OraSauro
Posts: 611
Joined: Mon, 19 Jan 2009 4:29 pm
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

It's as I said earlier: If two users perform their transaction at the same time, it will stick, package variable does not have read or write insulation.

Work does not mean that it is correct.

Embrace,
Francisco.
User avatar
fsitja
Rank: OraSauro
Rank: OraSauro
Posts: 611
Joined: Mon, 19 Jan 2009 4:29 pm
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

Did you test update multiple rows at once? The package will overwrite the values ??of a line over the other.
Thyago Oliveira
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 1
Joined: Wed, 21 Aug 2013 10:45 am

Dr_Gori I'm following the topic discussion and an idea came to me. And if I declare Pragma AutoNomous_Transaction on the trigger to create a separate transaction and at the end give the "Commit;" ?
Post Reply
  • Information
  • Who is online

    Users browsing this forum: Majestic-12 [Bot] and 12 guests