Get trigger value

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
Bene
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 7
Joined: Fri, 06 Jun 2008 9:34 am
Location: Sorocaba-SP

Hi. I have a problem.
How do I get the values ??of the last line I'm inserting to play for another table?
I'm trying to make a trigger more is failing.

How do I make a trigger send the last line entered to a log table?

Thanks

Rafael R. Machado
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 your trigger, throw the value of : New.Campo on a log table.
Would this be?
Bene
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 7
Joined: Fri, 06 Jun 2008 9:34 am
Location: Sorocaba-SP

Good morning, my friend

So, that's exactly what I want to do just can not do.
is very simple, but I'm picking up here.

I created a test table, and in it I will play the email address that I am inserting into another test table.
Please, what's wrong here?

The email table is thus
userid, mailaddr

in the trigger I am doing in this table I am doing this:

Select all

 
CREATE OR REPLACE 
TRIGGER TRIGGER1 
 AFTER INSERT ON MAIL 
 REFERENCING OLD AS old NEW AS new  
BEGIN 
  insert into TABEL1 (COLUMN1,COLUMN2) values (:new.USERID, (select sequence1.nextval from dual)); 
END; 
But it always results in this error here:

Select all

Error: ORA-04082: NEW or OLD references not allowed in table level triggers
What is wrong here?

Thanks for the help, man, always you respond to my posts Uhaua
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, follow some examples:

Select all

 
 
CREATE TABLE teste (id number(05), email varchar2(50)); 
 
CREATE TABLE teste2 (email VARCHAR2(50)); 
 
CREATE OR REPLACE TRIGGER TESTE_TR_01 
 AFTER  
 INSERT 
 ON TESTE 
 REFERENCING OLD AS OLD NEW AS NEW 
 FOR EACH ROW  
BEGIN 
 INSERT INTO TESTE2(EMAIL) 
  VALUES(:NEW.EMAIL); 
END; 
 
INSERT INTO TESTE (id, email) VALUES (1,'paulotrevisolli@ig.com.br'); 
 
COMMIT; 
 
SELECT * FROM teste; 
 
SELECT * FROM teste2; 
If it does not work, send us to us.
Bene
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 7
Joined: Fri, 06 Jun 2008 9:34 am
Location: Sorocaba-SP

We're almost expensive.

only has a problem, I have a sequence that has to control the log table IDs.
How do I declare a variable that can have the value of this sequence?
Example:

Select all

CREATE OR REPLACE 
TRIGGER TRIGGER1 
 AFTER INSERT ON MAIL 
 REFERENCING OLD AS old NEW AS new  
 for each row 
DECLARE 
  IDEvent int; 
BEGIN 
   IDEvent :=  select sequence1.nextval from dual; 
 
  insert into TABLE1 (COLUMN1,COLUMN2); 
  values (IDEvent,:new.USERID); 
END;
This code here in error.
How do I declare variables in Oracle?

Thanks for the man help.

Rafael R. Machado
Sorocaba
Bene
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 7
Joined: Fri, 06 Jun 2008 9:34 am
Location: Sorocaba-SP

I got people here.

Thanks for aid there

Rafal R. Machado
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,

Here is an example, for those who may need:

Select all

 
    DECLARE  
      vseq PLS_INTEGER; 
    BEGIN 
      SELECT TUASEQUENCE.NEXTVAL 
        INTO vseq 
       FROM dual; 
    EXCEPTION 
      WHEN OTHERS THEN 
        -- Trata tuas exceções aqui...     
    END;   
bvinicius
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 1
Joined: Tue, 13 May 2014 9:20 pm

Good evening,

I looked for some open topic about it and what I found closer was this.

Work as a programmer and use SQL bank and now in college I am using Banco Oracle and picking up a lot rsrs.

My difficulty is in the following:

I need to record in a field the description plus the sequential cod that Sequence generates through NEXTVAL, I tried several ways :

Assigning to a variange, eg

Select all

v_num := s_saida.nextval
Concatenating directly in the INSERT EX:

Select all

'descricao ' + to_char(s_saida.nextval)
and through a SELECT also .

But the following error is returned:

The reference S_SAIDA.NETNETVAL to the table, view or sequence not allowed in this context.

Someone could tell me, what is the correct way to manipulate the next to the next and concatene it with some more information?

Thank you in advance.
Renato Menezes Viana
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 128
Joined: Sat, 18 Nov 2006 11:51 am
Location: Rio de Janeiro - RJ

Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest