Solved

Oracle trigger (insert multiple rows into table)

Posted on 2014-11-13
12
675 Views
Last Modified: 2014-11-15
Greeting,
I have a trigger in Oracle database. It select a single record from one table and insert into another table. Now the select might return more than 1 record. Please suggest.

//Below is the code it select a record from table
BEGIN
          select phone into pp_nextel_phone
          where shop_name=:new.shop;
          EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
          pp_nextel_phone   := null;
          pp_mssg_type      :=null;
          END;

//below is the code I insert into another table
my_nextel_phone:=pp_nextel_phone;
 insert into mssg_log(PHONE,mssg_type,PCODE,PHSDSC,extra_dsc)
          values(my_nextel_phone,my_mssg_type,p_Problem_code,:new.description,:new.long_desc);
0
Comment
Question by:mrong
  • 4
  • 4
  • 2
  • +1
12 Comments
 
LVL 1

Expert Comment

by:ora-600
ID: 40440216
Hi,
you mustn't return multiple results. This will fail.
You should extend your exception handling and handle multiple results there.
Regards
0
 

Author Comment

by:mrong
ID: 40440241
Can you provide sample code? thanks.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40440245
what do you want it to do when the select returns more than one row?
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:mrong
ID: 40440260
keep inserting into table mssg_log.
0
 
LVL 1

Expert Comment

by:ora-600
ID: 40440293
well, but what do you want to insert if you get 2 or 5 results?
- the first?
- the last?
- the average?
0
 

Author Comment

by:mrong
ID: 40440317
insert all of the matching phone into table. thanks.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40440370
don't break it into multiple steps, instead of selecting into variable and then inserting those variables

just insert from the select directly

 insert into mssg_log(PHONE,mssg_type,PCODE,PHSDSC,extra_dsc)
select phone, my_mssg_type,p_Problem_code,:new.description,:new.long_desc
from your_table
where  shop_name=:new.shop;
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40440950
I agree with sdstuber, that usually it is best to use SQL to solve your data problems directly in SQL.  But, if you want to use a PL\SQL cursor for this for some reason, use a cursor loop with an insert in the loop.
0
 

Author Comment

by:mrong
ID: 40441056
sdstuber,
this will work even the select returns multiple records?
Thanks.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40441073
>> this will work even the select returns multiple records?

yes


simple test to illustrate

create table testtable (n number);
insert into testtable select object_id from all_objects;

Open in new window


testtable should have many rows in it after the insert
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40441109
I would recommend changing the insert in that test to this:
insert into testtable select object_id from all_objects
where rownum < 11;

That will prove the point without wasting a lot of time (and disk space).
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40441141
It only took 3 seconds for the table to be created on my test db (61k rows) and it's small only 1mb.

They probably had the test already completed and dropped up before either of these responses came in.

but yes, 10 rows would have been sufficient
I kind of wanted to show it working for a bunch though.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PL SQL Developer 7 37
SSRS 2013 - Creating a summarized report 19 37
Query Syntax 17 36
Oracle Query - Return results based on minimum value 8 32
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question