Solved

Oracle trigger (insert multiple rows into table)

Posted on 2014-11-13
12
628 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
 

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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 34

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 34

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now