| From: | Jamie Deppeler <jamie(at)doitonce(dot)net(dot)au> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: update in triggers |
| Date: | 2005-01-19 05:45:14 |
| Message-ID: | 41EDF3EA.1000903@doitonce.net.au |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
What i am trying to do is to update a field based on a sql query<br>
<pre wrap="">set notes='hello' is just being used as a test but i can not seem to make this simple update work
</pre>
<br>
<br>
Michael Fuhr wrote:
<blockquote cite="mid20050119053153(dot)GA56649(at)winnie(dot)fuhr(dot)org" type="cite">
<pre wrap="">On Wed, Jan 19, 2005 at 03:45:53PM +1100, Jamie Deppeler wrote:
</pre>
<blockquote type="cite">
<pre wrap="">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
</pre>
</blockquote>
<pre wrap=""><!---->
recursion, noun. See recursion.
</pre>
<blockquote type="cite">
<pre wrap="">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";
</pre>
</blockquote>
<pre wrap=""><!---->
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?
</pre>
<blockquote type="cite">
<pre wrap=""> return null;
end;
'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
</pre>
</blockquote>
<pre wrap=""><!---->
A function that has side effects like updating a table should be
VOLATILE, not IMMUTABLE.
</pre>
</blockquote>
<br>
</body>
</html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 2.0 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Michael Fuhr | 2005-01-19 05:50:56 | Re: Getting table metadata |
| Previous Message | Mike G. | 2005-01-19 05:34:41 | Re: update in triggers |