Re: Trigger error

really need more info about your process, plus this trigger looks incomplete

basically your no data found is going to refer to SUPTR, where you're looking for two records that match values from the record your about to insert into MASUPTR

are you sure both records exist? if one does and one doesn't, you'll get the NO_DATA_FOUND if that's the case you'll probably need to do two selects, then check the values with PL/SQL expressions

couple suggestions:
[_] you don't need the REFERENCING clause unless you're changing the NEW and OLD references
[_] you don't need the GROUP BY clause unless your using an aggregate function like MAX, SUM, etc (GREATEST is a scalar function) [_] always include an exception handler in your code to catch expected exceptions, or document that there are none [_] consider using explicit cursors for single-row selects -- i believe it's still true that they have just a bit less overhead than an implicit cursor

-- 
Mark C. Stock
email mcstock -> enquery(dot)com
www.enquery.com
(888) 512-2048

"Patrik" <pama_at_unitema.se> wrote in message
news:377af206.0310311446.29c24a3_at_posting.google.com...


> 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