pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTrigger issue, bug? on 7.2.1
5+ messages / 3 participants
[nested] [flat]
* Trigger issue, bug? on 7.2.1
@ 2003-03-18 13:46 Miguel Carvalho <miguel@ipatimup.pt>
2003-03-18 14:34 ` Re: Trigger issue, bug? on 7.2.1 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Miguel Carvalho @ 2003-03-18 13:46 UTC (permalink / raw)
To: pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trigger issue, bug? on 7.2.1
2003-03-18 13:46 Trigger issue, bug? on 7.2.1 Miguel Carvalho <miguel@ipatimup.pt>
@ 2003-03-18 14:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-20 12:55 ` Re: Trigger issue, bug? on 7.2.1 Miguel Carvalho <miguel@ipatimup.pt>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2003-03-18 14:34 UTC (permalink / raw)
To: miguel@ipatimup.pt; +Cc: pgsql-general@postgresql.org
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trigger issue, bug? on 7.2.1
2003-03-18 13:46 Trigger issue, bug? on 7.2.1 Miguel Carvalho <miguel@ipatimup.pt>
2003-03-18 14:34 ` Re: Trigger issue, bug? on 7.2.1 Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-03-20 12:55 ` Miguel Carvalho <miguel@ipatimup.pt>
0 siblings, 0 replies; 5+ messages in thread
From: Miguel Carvalho @ 2003-03-20 12:55 UTC (permalink / raw)
To: pgsql-general@postgresql.org; +Cc: pgsql-sql
> "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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Trigger issue, bug? on 7.2.1
@ 2003-03-18 14:15 Miguel Carvalho <miguel@ipatimup.pt>
0 siblings, 0 replies; 5+ messages in thread
From: Miguel Carvalho @ 2003-03-18 14:15 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trigger issue, bug? on 7.2.1
@ 2003-03-18 15:44 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 5+ messages in thread
From: Christoph Haller @ 2003-03-18 15:44 UTC (permalink / raw)
To: pgsql-sql; +Cc: miguel@ipatimup.pt
>
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-03-20 12:55 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-18 13:46 Trigger issue, bug? on 7.2.1 Miguel Carvalho <miguel@ipatimup.pt>
2003-03-18 14:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-20 12:55 ` Miguel Carvalho <miguel@ipatimup.pt>
2003-03-18 14:15 Trigger issue, bug? on 7.2.1 Miguel Carvalho <miguel@ipatimup.pt>
2003-03-18 15:44 Re: Trigger issue, bug? on 7.2.1 Christoph Haller <ch@rodos.fzk.de>
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