Обсуждение: Trigger function
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.
Mike
On 9 March 2014 01:26, Michael Rowan <michael.rowan3@gmail.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