Trigger function not executing

From: "Donald Fraser" <demolish(at)cwgsy(dot)net>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Trigger function not executing
Date: 2003-02-06 11:27:13
Message-ID: 003501c2cdd2$b55ca890$1664a8c0@DEMOLITION
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


OS: Redhat, kernel 2.4
PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC 2.96

I have a table that has two triggers functions applied to it
1) CREATE TRIGGER trig_delete_user BEFORE DELETE ON tbl_user FOR EACH ROW EXECUTE PROCEDURE delete_user();
2) CREATE TRIGGER trig_insert_sys_user AFTER INSERT OR DELETE OR UPDATE ON tbl_user FOR EACH ROW EXECUTE PROCEDURE insert_sys_user();

The BEFORE trigger function basically does nothing to modify the tuple in any way. It does run a SELECT on the same table to do some routine checks and before it can exit the function it sets a global flag to tell the AFTER trigger function what to do.

The AFTER trigger function resets the flag set by the BEFORE function and then performs some administration commands for users . In this instance it issues a "DROP USER" command.

I've included the above summary as an insight to where the problem may be.

Here is the problem:
If I run SQL such as DELETE FROM tbl_user WHERE id = '2'; then I don't get any problems.
If I run SQL such as DELETE FROM tbl_user WHERE id > '40'; and there is only one record that meets the condition (id > '40') then I don't get any problems.
If I run SQL such as DELETE FROM tbl_user WHERE id > '20'; and there is more than one record that meets the condition (id > '20') then I get the following problem. The BEFORE trigger function is only being called on the first record that is being deleted yet the AFTER function is being called on all of the records - well in this case it only gets to the second record and fails because the flag has not been set by the BEFORE function. Just to re-iterate - the BFORE function is very simple - it cannot exit the function without setting the flag I have mentioned.

Have I misunderstood trigger functions or is this a legitimate bug?

Regards
Donald Fraser

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2003-02-06 14:42:49 Re: Trigger function not executing
Previous Message Yaniv Hamo 2003-02-06 09:26:44 PG do not accept quoted names for tables/columns