Dear experts,
I am developing a VB6.0 application with Oracle 8.1.7 (and oo4o 8.1.7 installed)
Please help me to write a trigger for this.
I have a table
create table inventory(itemid number,
course varchar2(10),
description varchar2(30),
price number(8,2),
primary key(itemid, course));
one itemid can belong to many courses. So itemid and course is the primary key. But same itemid should have same description and same price.
So whenever one row is inserted or updated, those rows having the same itemid should be updated regarding the description and price.
My trigger didn't work. Because i am dealing with a mutating table. Can someone help me to solve this problem?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Hi,
If you are so keen on the trigger, I think this can help.
create or replace trigger inv
before insert on inventory
for each row
begin
select description,price into :new.description,:new.pric
where itemid = :new.itemid;
exception
WHEN OTHERS THEN
dbms_output.put_line('erro
end;
/
bala.
You will probably run into mutating table errors if you try to do this with a single trigger.
To make triggers work, you can see some solutions here: http://osi.oracle.com/~tky
Hi huer,
The required trigger will be:
create or replace trigger inv
before insert on inventory
for each row
begin
update inventory set description=:new.descripti
exception
WHEN OTHERS THEN
dbms_output.put_line('erro
end;
/
commit;
Try this out, it will solve ur problem.
Dear: nisal
I've rejected your proposed answer as Experts Exchange holds an experiment to work without the answer button.
See: http://www.experts-exchang
Paragraph: Site Update for Wednesday, November 06, 2002
By this rejection the Asker will be notified by mail and hopefully he will take his responsibility to finalize the question or post an additional comment.
The Asker sees a button beside every post which says "Accept This Comment As Answer" (including rejected answers) -- so if he/she thinks yours is the best, you'll be awarded the points and the grade.
Thanks !
modulo
Community Support Moderator
Experts Exchange
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
PAQ'd and Points Forfeited
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
SDutta
EE Cleanup Volunteer
Business Accounts
Answer for Membership
by: MikeOM_DBAPosted on 2002-10-17 at 11:25:06ID: 7341754
Why not just do the following in your application?:
update inventory
set description = :p_desc, price = :p_price
where itemid = :p_itemid;