>
> 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 blocksIF 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