Re: Trigger function

From: Thom Brown <thom(at)linux(dot)com>
To: Michael Rowan <michael(dot)rowan3(at)gmail(dot)com>
Cc: pgsql-novice novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Trigger function
Date: 2014-03-09 02:14:15
Message-ID: CAA-aLv5X2Pt=TpQPO+4=zpspSS8mYaNuur+i9UY_A+an+rov+w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Anh Pham 2014-03-10 14:09:57 [NOVICE]Convert Datum to an user-defined data type?
Previous Message Michael Rowan 2014-03-09 01:26:52 Trigger function