Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Help
Trigger Help [message #248019] Wed, 27 June 2007 15:43 Go to next message
BJerolimic
Messages: 2
Registered: June 2007
Junior Member
Trying to build a trigger that will update fed_paid only when tax.taxrec_typ = 13 (which is equivalent of exercise.exer_type = 4). Please note that data comes from exercise, grant & tax table

Select statement returns correct info, but user gets error when trying to put in new record via front-end


And FYI the Front-end is s C++ app called EOWin

Error message is: ORA=01422: exact fetch returns more than requester number of rows
ORA-06512: at "AET.SARS_RESIDUAL", line 7
ORA-04088: error d


TRIGGER SARs_Residual
BEFORE INSERT ON tax
FOR EACH ROW
WHEN (new.taxrec_typ = 13)
DECLARE
v_fed_paid number(30,15);

BEGIN

-- Find new Fed Paid number
SELECT DECODE(TRUNC((fed_paid + state_paid + loc1_paid + loc2_paid + loc3_paid + loc4_paid + loc5_paid + loc6_paid + sosec_paid + med_paid)/e.mkt_prc),

(fed_paid + state_paid + loc1_paid + loc2_paid + loc3_paid + loc4_paid + loc5_paid + loc6_paid + sosec_paid + med_paid)/e.mkt_prc,

fed_paid + (fed_paid + state_paid + loc1_paid + loc2_paid + loc3_paid + loc4_paid + loc5_paid + loc6_paid + sosec_paid + med_paid)/e.mkt_prc * e.mkt_prc - e.mkt_prc * (g.opt_prc * e.opts_exer / e.mkt_prc - TRUNC(g.opt_prc * e.opts_exer / e.mkt_prc, 0)),

fed_paid + (TRUNC((fed_paid + state_paid + loc1_paid + loc2_paid + loc3_paid + loc4_paid + loc5_paid + loc6_paid + sosec_paid + med_paid)/e.mkt_prc) + 1 - (fed_paid + state_paid + loc1_paid + loc2_paid + loc3_paid + loc4_paid + loc5_paid + loc6_paid + sosec_paid + med_paid)/e.mkt_prc) * e.mkt_prc - e.mkt_prc * (g.opt_prc * e.opts_exer / e.mkt_prc - TRUNC(g.opt_prc * e.opts_exer / e.mkt_prc, 0)))

INTO v_fed_paid
FROM exercise e, grantz g, tax t
WHERE g.grant_num = e.grant_num AND e.exer_num = t.exer_num AND taxrec_typ = 13;

:new.fed_paid := v_fed_paid;

END;

[Updated on: Wed, 27 June 2007 16:03]

Report message to a moderator

Re: Trigger Help [message #248050 is a reply to message #248019] Wed, 27 June 2007 21:05 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
Error message is: ORA=01422: exact fetch returns more than requester number of rows


Cross check the select whether it is returning one row or not.

By
Vamsi
Re: Trigger Help [message #248083 is a reply to message #248019] Thu, 28 June 2007 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition:
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Trigger Help [message #248244 is a reply to message #248050] Thu, 28 June 2007 07:28 Go to previous messageGo to next message
BJerolimic
Messages: 2
Registered: June 2007
Junior Member
vamsi kasina wrote on Wed, 27 June 2007 22:05
Quote:
Error message is: ORA=01422: exact fetch returns more than requester number of rows


Cross check the select whether it is returning one row or not.

By
Vamsi



ah-ha... you are right, it returns 20 rows!

how do I get it to only return values from the new records in exercise & tax that are being created by the front-end?

can I do something like:

and e.exer_num = :new.exer_num ???
Re: Trigger Help [message #248246 is a reply to message #248019] Thu, 28 June 2007 07:34 Go to previous message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Well why don't you try it and see.
Previous Topic: Create trigger and raise application for Invalid login (merged)
Next Topic: simple sql
Goto Forum:
  


Current Time: Sat Dec 10 01:18:09 CST 2016

Total time taken to generate the page: 0.28141 seconds