Trigger function

From: Michael Rowan <michael(dot)rowan3(at)gmail(dot)com>
To: pgsql-novice novice <pgsql-novice(at)postgresql(dot)org>
Subject: Trigger function
Date: 2014-03-09 01:26:52
Message-ID: CAL04Mkk4+JaEkEW8DrxwSvv5YfxM6UCWM-wa1LeAW2wuTMUKPw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Thom Brown 2014-03-09 02:14:15 Re: Trigger function
Previous Message Amol Bhangdiya 2014-03-08 15:35:05 Re: Selection of join algorithm