Re: How to avoid the Mutating Triggers ??

On Thu, 20 Feb 2003, Yudha Setiawan wrote:

> Dear Szabo, You were wrote;
>
> > Well, what have you tried? For the simple
> > case (pointer is not changing), a not terribly
> > efficient version might be something like:
> >
> > create or replace function dorecalcupd() returns trigger as
> > 'declare
> > last int;
> > begin
> > SELECT INTO last qty FROM foo where pointer < NEW."pointer" ORDER BY
> > pointer desc LIMIT 1;
> > NEW.qty := last + NEW."in" - NEW."out";
> > if (NEW.pointer = OLD.POINTER AND (NEW."in" != OLD."in" or NEW."out" !=
> > OLD."out")) then
> > UPDATE foo set qty = (select sum(f."in") - sum(f."out") from foo f where
> > f.pointer<pointer)
> > where pointer>NEW.pointer;
> > end if;
> > return NEW;
> > end;' language 'plpgsql';
> >
> > In this case the trigger does cause requests to the same trigger for the
> > later rows, but since only qty is changing it does nothing.
> >
> > It's more complicated when you deal with the pointer moving case.
>
> Thank's a Lot for all milist especialy for dear Stephan Szabo. I'm Sorry
> "Pointer" on my ilustration "IS NOT a FIELD". it's just information for
> me, to show you where is exactly the position of record / Pointer that
> I want to Change.
> Perhaps you have another best one, please...??

Well, what's the key that you're ordering by? Use that instead of pointer
in the above.

If you have no ordering key or the key isn't guaranteed to be unique,
the original question doesn't have an answer because there is no implict
order to rows in a table.