After Trigger Select into issue

jyothsna1803
jyothsna1803 used Ask the Experts™
on
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 and 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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
This is your problem:
 SELECT t.id
   INTO t1ID
   FROM Table1 t
 WHERE AND trim(upper(Dname)) = trim(upper(:new.NAME))
 

Author

Commented:
SELECT t.id
  INTO t1ID
  FROM Table1 t
 WHERE   trim(upper(Dname)) = trim(upper(:new.NAME))
Sorry i am using this query only, just modified the table names, So i missed out this end. Still i have the issue
HainKurtSr. System Analyst
Commented:
what is the problem, do you get this

raise_application_error(-20004, 'Error occured! New Row ID = ' || :new.ID );

or something else?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

HainKurtSr. System Analyst

Commented:
try this, maybe the column name "NAME" is the problem...
SELECT   t.id
  INTO   t1ID
  FROM   Table1 t
 WHERE   TRIM (UPPER (Dname)) = TRIM (UPPER (:new."NAME"))

Open in new window

HainKurtSr. System Analyst

Commented:
or maybe you are getting multiple record...
get the first record for a workaround, or, fix your data in Table1, add a nuque function index on trim(upper(NAME))
SELECT   t.id
  INTO   t1ID
  FROM   Table1 t
 WHERE   TRIM (UPPER (Dname)) = TRIM (UPPER (:new."NAME")) and rownum=1

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Is what you posted the EXACT code you are trying to execute?

If you're getting syntax errors and your trigger won't compile, you're missing some semi-colons:

The one after the select in question and one after the insert:

Select t.id INTO t1ID from Table1 t  where and trim(upper(name)) = trim(upper(:new.NAME));
         
  Insert into Table3(ID,t1ID,t2ID,Active,CreatedDate) Values
     (t3Seq,t1ID,:new.ID,1,sysdate);
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'

.But i figure out the issue. I was getting T1 ID based on T1 & T2 Join. So i got this error.
raise_application_error(-20004, 'Error: ' || sqlerrm ); statement helped me to figure out the exact issue.

 Thank You.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
If you solved this question yourself you can ask that it be closed and have the points refunded by clicking the 'Request Attention' link above.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial