Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Trigger error
Patrik wrote:
>Hi I'm working with an ERP-system wich is upating a table incorrect.
>The workaround is creating an trigger. But I ran into some problems,
>the trigger I've created looks like this:
>
>CREATE OR REPLACE TRIGGER F0003.MASUPTR_PAYEDDT
> BEFORE INSERT on F0003.MASUPTR
> REFERENCING NEW AS NEW OLD AS OLD
> FOR EACH ROW
>BEGIN
>Select greatest(ST1.VoDt,ST2.VoDt) VoDt
> into :New.PayedDt
> from SupTr ST1, SupTr ST2
> where ST1.JNo = :New.AgJno
> and ST1.EntNo = :New.AgEntNo
> and ST2.JNo = :New.JNo
> and ST2.EntNo = :New.EntNo
> group by :New.AgJNo,:New.AgEntNo, ST1.VoDt,ST2.VoDt;
>END;
>
>when I'm doing the settlement of invices in the ERP-System I recive
>"ORA-01403: no data found". I know for a fact that the system creates
>a record for each settlement of invoices in the table MaSupTr. Is the
>problem using "before insert" together with "select into"??
>
>Thanks for your help !! Regards /Patrik
>
>
Sounds to me like some judicious use of NVL might solve the problem.
-- Daniel Morgan http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp damorgan_at_x.washington.edu (replace 'x' with a 'u' to reply)Received on Fri Oct 31 2003 - 17:02:30 CST