Thread: How to avoid the Mutating Triggers ??

On Wed, 19 Feb 2003, Yudha Setiawan wrote:

> How do I "recalculate" one of
> my field, if i updated one of
> the record that's have position
> in the middle of all record.
> Here my Ilustration is;
> ----------------------------------------
> Poin-|FdDate     | Code| IN  |OUT| Qty |
> ter. |----------------------------------
> 1    |01/01/2003 | 001 |  5  | 0 |   5 |
> 2    |01/02/2003 | 001 |  3  | 0 |   8 |
> 3    |01/03/2003 | 001 |  0  | 1 |   7 |
> 4    |01/06/2003 | 001 | 100 | 0 | 117 |
> 5    |00107/2003 | 001 |  0  | 1 | 118 |
> 6    |00108/2003 | 001 |  0  | 1 | 119 |
> 7    |00109/2003 | 001 |  0  | 1 | 120 |
> ----------------------------------------
> And what i want is, update the record
> on Pointer no 4. on "IN" is changed from
> 100 to 10 And Qty is Automaticly change
> from 117 to 17. like this;
> ----------------------------------------
> Poin-|FdDate     | Code| IN  |OUT| Qty |
> ter. |----------------------------------
> 4    |01/06/2003 | 001 | 10  | 0 |  17 |
> ----------------------------------------
> Following with record below it, and become
> like this;
> ----------------------------------------
> Poin-|FdDate     | Code| IN  |OUT| Qty |
> ter. |----------------------------------
> 5    |00107/2003 | 001 | 0  | 1 |  18  |
> 6    |00108/2003 | 001 | 0  | 1 |  19  |
> 7    |00109/2003 | 001 | 0  | 1 |  20  |
> ----------------------------------------
>
> I've been trying using cursor inside of
> my trigger, It didn't work and it's
> "mutating". i thought my trigger is
> working recursive. It's very Urgent
> for me. I've been migrating Database
> from Ms-SQL, so far so great, till
> this Problem I've been spending my
> 3 Days with No-Result.
> It's time to Give-up.
> So gimme your hand please...???

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.

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

Thank You very much and,
GOD Bless You All.

Yudha Setiawan.
VoIP
    No.        : 628899911223344

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.