Trigger help

[BJerolimic]

Trying to build a trigger that will update fed_paid only when tax.taxrec_typ = 13 (which is equivalent of exercise.exer_t ype = 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_RESID UAL", 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((f ed_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_pai d + 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;

[debasisdas]

Your select statment selects more than one record and you are trying to store the same in a single variable.

[BJerolimic]

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 ???