Re: 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.