Re: update in triggers

On Wed, Jan 19, 2005 at 03:45:53PM +1100, Jamie Deppeler wrote:

> Think im doing something wrong here, cant seem to resolve the problem i
> have a trigger which is calling a update function and when it gets to a
> update it goes into a infinite loop

recursion, noun. See recursion.

> CREATE TRIGGER "new_trigger" AFTER INSERT OR UPDATE
> ON "chargeratetest" FOR EACH ROW
> EXECUTE PROCEDURE "chargeratetest"();
>
> CREATE OR REPLACE FUNCTION "chargeratetest" () RETURNS trigger AS'
> begin
>
> UPDATE chargeratetest
> set notes=''hello''
> where new."primary" = chargeratetest."primary";

The trigger says to call the function after every insert or update
on the table. Suppose you insert a record into the table. The
trigger calls the function and the function executes UPDATE. The
update causes the trigger to call the function, which executes
UPDATE so the trigger calls the function, which executes UPDATE so
the trigger calls the function, and so on. Infinite recursion.

What are you trying to do? What's the trigger's purpose?

> return null;
> end;
> 'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

A function that has side effects like updating a table should be
VOLATILE, not IMMUTABLE.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/