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