Re: Trigger function

On 9 March 2014 01:26, Michael Rowan <michael(dot)rowan3(at)gmail(dot)com> wrote:
> Hi
>
> Although a very noviceish person when it comes to PostgreSQL 9.1, I have
> successfully used trigger functions very similar to the following, but this
> one is not liked for some reason.
> My intention is to update an integer column po_sub_invoice_nr based on the
> value in another integer column po_invoice_nr.
> This is my trigger:
> CREATE TRIGGER inv_nr_ud_trig
> AFTER UPDATE
> ON purchaseorder
> FOR EACH ROW
> EXECUTE PROCEDURE inv_nr_ud_trig_fn()
>
> CREATE OR REPLACE inv_nr_ud_trig_fn()
>
> RETURNS trigger AS $body$
>
> BEGIN
>
> UPDATE purchaseorder SET po_sub_invoice_nr =
> mod(NEW.po_invoice_nr,10000)
>
> WHERE po_id=OLD.po_id;
>
> RETURN NEW;
>
> END;
>
> $body$
>
> LANGUAGE plpgsql VOLATILE
>
> COST 100;
>
> This results in a stack overflow and, in pgAdminIII the following return
> repeated many many times:
>
>
> SQL statement "UPDATE purchaseorder SET po_sub_invoice_nr =
> mod(NEW.po_invoice_nr,10000) WHERE po_id=OLD.po_id"
>
> PL/pgSQL function "inv_nr_ud_trig_fn" line 4 at SQL statement
>
>
> I'd be grateful for any pointers.

The trigger is calling a function which is updating a table, which is
calling a trigger, which is calling a function, etc.

Don't update the table that the trigger is on from within the trigger
function. Instead, just set the value of the column in NEW to what
you wanted:

CREATE OR REPLACE FUNCTION inv_nr_ud_trig_fn()
RETURNS trigger AS $body$
BEGIN
NEW.po_sub_invoice_nr := mod(NEW.po_invoice_nr,10000);
RETURN NEW;
END;
$body$
LANGUAGE plpgsql VOLATILE
COST 100;

And you'll need to change your trigger to a BEFORE trigger, as you'll
be changing the data before it hits the table. An AFTER trigger can't
do that.

Note that if someone explicitly set po_invoice_nr to a new value, this
trigger would re-assign a value based on the logic in the function.
Also an INSERT statement wouldn't cause this trigger to fire.
--
Thom