Solved

Oracle trigger (insert multiple rows into table)

Posted on 2014-11-13
12
721 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 74

Expert Comment

by:sdstuber
ID: 40440245
what do you want it to do when the select returns more than one row?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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 74

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 74

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 74

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

737 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