Thread: Trigger issue, bug? on 7.2.1

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?

Please fell free to ask more information if needed.

Miguel Carvalho

"Miguel Carvalho" <miguel@ipatimup.pt> writes:
> The trigger code will force another trigger fire because of the changes
> in the record. I'm i right?

No, altering the NEW record doesn't cause any additional trigger
firings.  If it did, such a trigger would be infinitely recursive.

Your DELETE issued by the trigger would cause firing of ON DELETE
triggers, but not another firing of ON UPDATE.

            regards, tom lane

> "Miguel Carvalho" <miguel@ipatimup.pt> writes:
>> The trigger code will force another trigger fire because of the
>> changes in the record. I'm i right?
>
> No, altering the NEW record doesn't cause any additional trigger
> firings.  If it did, such a trigger would be infinitely recursive.
>

I see your point, you are completely right!

> Your DELETE issued by the trigger would cause firing of ON DELETE
> triggers, but not another firing of ON UPDATE.

You are right, too.

When i was repplying to your message, after stripping some lines of the
trigger code, i have found that the second trigger fire ( not actualy a
real trigger fire ) was caused by a bad logic in the trigger code.

Thank's to all for the repplies.

Regards
Miguel Carvalho