Trigger does not run, charges mutant table.

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
pyro
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 323
Joined: Thu, 21 Sep 2006 10:21 am
Location: Barala - TT

I have a table that when inserting data it needs to shoot a trigger to collect the data of it and another table (both are related by an ID) and insert into another table, when I try to do this the trigger accuses that the table is mutant and gives error. Can anyone give a force?
look at the code of my trigger:
Code:

Select all

 
create or replace trigger insert_tabela_c 
  after insert on tabela_b  
  for each row 
declare 
  data         date; 
  peso         number(10,4); 
  precisao     varchar(1); 
  maquina      number(6); 
  produto      number(5); 
begin 
  select cod_aca, cod_maquina 
         into produto, 
              maquina 
  from tabela_a 
  where amo_codigo = :new.amo_codigo; 
  
  select ame.ame_data, ame.ame_peso, ame.ame_precisao 
         into data, 
              peso, 
              precisao 
  from tabela_b ame 
  where amo_codigo = :new.amo_codigo; 
 
  insert into tabela_c(data_med, peso, precisao, cod_maquina, cod_aca) 
         values(data, peso, precisao, maquina, produto); 
          
end insert_tabela_c; 
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

The problem is that you are doing select in table_b, and the trigger is from table_b. This oracle does not allow.

To work around, there are a number of forms.
See these: [[0] http://en.glufke.net/oracle/viewtopic.php?t=1009
: -O
pyro
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 323
Joined: Thu, 21 Sep 2006 10:21 am
Location: Barala - TT

I have seen this topic, I even used the tips contained there, but it happened to accuse that the data does not exist.

Sorry for my ignorance, but do you know a correct way to pick up the data from the table that fired the trigger to insert them into another table?
pyro
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 323
Joined: Thu, 21 Sep 2006 10:21 am
Location: Barala - TT

This problem of this topic I managed to solve, thanks for the force.
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Pyro, beleza BROTHER?

If the resolution has not been a solutions presented in the links, by Gori, please post as solved, so that others may have as reference in case they require.

grateful,
pyro
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 323
Joined: Thu, 21 Sep 2006 10:21 am
Location: Barala - TT

Well here is how the final trigger and working:

Select all

 
create or replace trigger insert_medicoes 
  after insert on amostragem_medicao   
  for each row 
declare 
  maquina      number(6); 
  produto      number(5); 
begin 
  select cod_aca, cod_maquina  
         into produto,  
              maquina 
  from amostragem 
  where amo_codigo = :new.amo_codigo; 
 
  insert into medicoes2(data_med, peso, precisao, cod_maquina, cod_aca) 
         values(:new.ame_data, :new.ame_peso, :new.ame_precisao, maquina, produto);      
end insert_medicoes; 
chrisinteract
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 5
Joined: Tue, 17 Jul 2007 11:02 am
Location: Lajeado - RS

And there!!!!!
I'm venturing into this oracle world and this is bringing a huge headache because I'm wanting to ride a trigger from a table that feeds a second with only two fields and I can not ... Example:
I have TT and XX tables.
TT Table fields are: Int and DT_Login Date, this when a record is inserted in this precise table q Trigger trigger playing the information on the second XX Q has the following fields: DD Date and TT int;
As I am a first-time sailor I would very much like an aid from the galera ...

Hugs
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Good morning,

You could be using the: New.Campo_Designed to insert in the table, as soon as you enter the first.

Look for Trigger in this forum, which will find some interesting examples, such as [url=http://en.glufke.net/oracle/viewtopic.p ... hlight=new]este
whatever doubt, send it there.
chrisinteract
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 5
Joined: Tue, 17 Jul 2007 11:02 am
Location: Lajeado - RS

Thanks for the Tips Travisolli ... !!!
But I'm breaking my head to a thing I believe is very easy but I'm not really able to walk on it ... Take a look and see if you can help me:

Create or Replace Trigger tr_tt
After Insert or Update on TT
For Each Row
Declare
INT;
DT_Login Date;
BEGIN
Select ref_user, dt_login
into = TT, XX
from TT
WHERE REF_USER = NEW_USER, DT_LOGIN = NEW_LOGIN


Embrace
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Do the following, try to adapt this example.
Tables: EMP + T_LOG (created below)



Then follows the trigger that is triggered whenever I enter or update the EMP :

Select all

 
CREATE OR REPLACE TRIGGER TR_INSERE_LOG 
 BEFORE  
 INSERT OR UPDATE 
 ON EMP 
 REFERENCING OLD AS OLD NEW AS NEW 
 FOR EACH ROW  
DECLARE 
V_SEQ    NUMBER(04) := 0; 
V_TP_ATU VARCHAR2(01); 
BEGIN 
 
  IF INSERTING 
  THEN 
    V_TP_ATU := 'I'; 
  ELSIF UPDATING 
  THEN 
    V_TP_ATU := 'U'; 
  END IF; 
 
  SELECT MAX(SEQ)+1 --- AQUI PODERIA SER UMA SEQUENCE TB. 
    INTO V_SEQ 
    FROM T_LOG; 
 
  INSERT INTO T_LOG 
       VALUES (V_SEQ, 
		         :NEW.EMPNO, 
					SYSDATE, 
					V_TP_ATU); 
EXCEPTION 
 WHEN OTHERS THEN 
   RAISE_APPLICATION_ERROR(-20001, 'Erro ao popular tabela de log (T_LOG) : ' || SQLERRM); 
END; 
 
I hope I have helped.
chrisinteract
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 5
Joined: Tue, 17 Jul 2007 11:02 am
Location: Lajeado - RS

Once again thank you Trevisolli, it helped yes and exclaimed ...

Thanks
chrisinteract
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 5
Joined: Tue, 17 Jul 2007 11:02 am
Location: Lajeado - RS

Hi everyone !!!
is accusing error in these synaxes:

Select all

 
declare 
        ref_user varchar(25); 
        dt_login varchar(12);         
 
begin 
    select count(*),count(*)  
        into ref_user,dt_login 
            from TT; 
    declare 
        tt varchar(25); 
        dd varchar(12); 
     
    begin 
        insert into XX 
            values (ref_use,:new.tt,dt_logins,:new.dd);  
    end;          
end; 
will anyone help me ????

Embrace
chrisinteract
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 5
Joined: Tue, 17 Jul 2007 11:02 am
Location: Lajeado - RS

Missed the error ...

> [Error] Script Lines: 1-17 ------------------------ -
ORA-01008: NOT ALL VARIABLES BOUND

Hugs
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Brother,

in Values, would not it be dt_login, not DT_Logins?
Karlinhoz
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 1
Joined: Mon, 25 Feb 2013 11:23 am

Friends I need help também ..... I'm starting with Oracle now and I can not solve this problem below, please help me:

Select all

CREATE OR REPLACE TRIGGER TBU_ENCERRAPROGNOSTICO 
BEFORE UPDATE OF arquivado1, arquivado2, arquivado3 
ON SAPC.PROCESSO 
REFERENCING NEW AS NEW OLD AS OLD 
FOR EACH ROW 
 
DECLARE 
 
vlnPROG NUMBER; 
 
BEGIN 
 
 SELECT NVL(COUNT(cod_interno),0) 
    INTO vlnPROG 
    FROM SAPC.PROCESSO 
   WHERE (cod_interno = :NEW.cod_interno) 
     AND PROB_EXITO In (1,2); 
      
IF(vlnPROG > 0) THEN 
 
      IF(:OLD.arquivado1||:OLD.arquivado2||:OLD.arquivado3 <> 'N' AND :NEW.arquivado1||:NEW.arquivado2||:NEW.arquivado3 = 'S') THEN 
      RAISE_APPLICATION_ERROR(-20003,'Não é permitido o encerramento de processo com prognóstico PROVÁVEL ou POSSÍVEL'); 
      END IF; 
 
END IF; 
 
END;

Even mutant table error.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: Google [Bot] and 1 guest