From: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
Cc: miguel@ipatimup.pt
Subject: Re: Trigger issue, bug? on 7.2.1
Date: Tue, 18 Mar 2003 16:44:03 +0100
Message-ID: <3E773EC2.66D7EEE7@rodos.fzk.de> (raw)
>
> Hi, we are having some trouble we a trigger.
>
> Or version is: PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC
> 2.96, running on RH72.
>
> Suppose this SQL query:
>
> UPDATE table set state=1 where id=30;
>
> Theres a trigger on the table ON UPDATE BEFORE
>
> The trigger code is something like this:
>
> IF NEW.state = 1 THEN
> RAISE NOTICE ''Trigger: % -> some administrative information '',
> TG_NAME; NEW.state=2;
> END IF;
>
> IF NEW.state = 2 THEN
> RAISE NOTICE ''Trigger: % -> some administrative information
> '',TG_NAME; DELETE FROM table where id = OLD.id;
> END IF;
>
> The trigger code will force another trigger fire because of the
changes
> in the record. I'm i right?
>
> The trouble is that the trigger isnt firing, the trigger only gets
> executed only once.
>
> If i do a select on the table the state field contains the value 2 not
> 1, so i'm shure the trigger as runned.
>
> Is this a trigger issue or i'm i doing something wrong?
>
Have you seen this taken from the 7.2 doc:
A trigger function must return either NULL or a record/row value having
exactly the structure of the table the trigger was fired for.
Triggers fired BEFORE may return NULL to signal the trigger manager to
skip the rest of the operation for this row (ie, subsequent
^^^^^^^^^
triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for
this row). If a non-NULL value is returned then
^^^^^^^^^^^^^^^^^
the
operation proceeds with that row value. Note that returning a row value
different from the original value of NEW alters the row
that will be inserted or updated. It is possible to replace single
values directly in NEW and return that, or to build a complete new
record/row to return.
In addition it appears strange to me what is going on
with these two IF blocks
IF NEW.state = 1 THEN
...
NEW.state=2;
END IF;
IF NEW.state = 2 THEN
...
END IF;
Whenever state=1 it is set to state=2, so the second IF is entered on
state=1 and state=2.
Is this intended?
Regards, Christoph
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, miguel@ipatimup.pt
Subject: Re: Trigger issue, bug? on 7.2.1
In-Reply-To: <3E773EC2.66D7EEE7@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox