SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

After Trigger Select into issue

634672Jan 29 2010 — edited Jan 29 2010

CREATE OR REPLACE TRIGGER "TAB2_AI"
AFTER INSERT ON Table2
FOR EACH ROW
DECLARE
t1ID number;
t3Seq number;
BEGIN

Select t.id INTO t1ID from Table1 t where trim(upper(name)) = trim(upper(:new.NAME))

SELECT Table3_SEQ.nextval into t3Seq from dual;

Insert into Table3(ID,t1ID,t2ID,Active,CreatedDate) Values
(t3Seq,t1ID,:new.ID,1,sysdate)
EXCEPTION
when others then
raise_application_error(-20004,
'Error occured! New Row ID = ' || :new.ID );
END TAB2_AI;
/

I am writing after Insert Trigger. I have 3 tables. I need to write after Insert on Table2.
But in my Table 3 i want to insert table1 ID and table2 ID and other info.
Now getting the table1 ID is giving the problem. Please See below statement. This is causing the problem.
Select t.id INTO t1ID from Table1 t where and trim(upper(Dname)) = trim(upper(:new.NAME))
Could you please tell me the work around how to put select into in a trigger? Any suggestions highly appreciated.

Edited by: Chris90909 on Jan 29, 2010 9:20 AM

Edited by: Chris90909 on Jan 29, 2010 9:29 AM

Peter K

How many rows are returned when you run the following SQL?

Select t.id from Table1 t where and trim(upper(Dname)) = trim(upper('String you are attempting to insert into Table 2'))

if no rows or more than 1 row then you will get an exception, as per your excpetion block

P;

634672

it is returning only one rwo. I have tested this query and it is returning only one row.

Andreas Weiden

Throw away that exception handler and show us the error which is shown then.

Peter K

could you modify your exception block as follows; and post the output?

raise_application_error(-20004, 'Error: ' || sqlerrm );

1 person found this helpful

731020

You said

Please See below statement. This is causing the problem.
Select t.id INTO t1ID from Table1 t where and trim(upper(Dname)) = trim(upper(:new.NAME))

But in your code you are using following query

Select t.id INTO t1ID from Table1 t where trim(upper(name)) = trim(upper(:new.NAME))

These are 2 different queries. In first one , in where clause you are using column name as Dname and the query you are using in your trigger has column name as name in where clause.

634672

ERROR at line 1:
ORA-20004: Error: ORA-04091: table Table2 is mutating,
trigger/function may not see it
ORA-06512: at "TAB2_AI", line 15
ORA-04088: error during execution of trigger 'TAB2_AI'

Peter K

appears to be your insert that is failing!

Do you have a trigger on Table 3?

P;

634672

No..But i figure out the issue. I was getting T1 ID based on T1 & T2 Join. So i got this error. Yourt Deb ug statement helped me to resolve the issue. Thank You.

1 - 8

Locked Post

New comments cannot be posted to this locked post.

Locked on Feb 26 2010

Added on Jan 29 2010

#oracle, #trigger

8 comments

2,674 views

SQL & PL/SQL

After Trigger Select into issue

Comments

Post Details