Skip to Main Content

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 26 2010
Added on Jan 29 2010
8 comments
2,666 views