Search Postgresql Archives

Re: update in triggers

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



What i am trying to do is to update a field based on a sql query
set notes='hello' is just being used as a test but i can not seem to make this simple update work


Michael Fuhr wrote:
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.

  


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux