Oracle trigger (insert multiple rows into table)

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);
mrongAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ora-600Oracle Database AdministratorCommented:
Hi,
you mustn't return multiple results. This will fail.
You should extend your exception handling and handle multiple results there.
Regards
0
mrongAuthor Commented:
Can you provide sample code? thanks.
0
sdstuberCommented:
what do you want it to do when the select returns more than one row?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

mrongAuthor Commented:
keep inserting into table mssg_log.
0
ora-600Oracle Database AdministratorCommented:
well, but what do you want to insert if you get 2 or 5 results?
- the first?
- the last?
- the average?
0
mrongAuthor Commented:
insert all of the matching phone into table. thanks.
0
sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
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
mrongAuthor Commented:
sdstuber,
this will work even the select returns multiple records?
Thanks.
0
sdstuberCommented:
>> 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
Mark GeerlingsDatabase AdministratorCommented:
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
sdstuberCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.