Forum Stats

  • 3,851,547 Users
  • 2,263,999 Discussions
  • 7,904,776 Comments

Discussions

After Trigger Select into issue

634672
634672 Member Posts: 96
edited Jan 29, 2010 2:02PM in SQL & PL/SQL
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
Tagged:

Answers

This discussion has been closed.