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 E=
XECUTE 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 a=
ny way. It does run a SELECT on the same table to do some routine checks an=
d before it can exit the function it sets a global flag to tell the AFTER t=
rigger function what to do.
The AFTER trigger function resets the flag set by the BEFORE function and t=
hen performs some administration commands for users . In this instance it i=
ssues 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 =3D '2'; then I don't ge=
t any problems.
If I run SQL such as DELETE FROM tbl_user WHERE id > '40'; and there is onl=
y one record that meets the condition (id > '40') then I don't get any prob=
lems.
If I run SQL such as DELETE FROM tbl_user WHERE id > '20'; and there is mor=
e than one record that meets the condition (id > '20') then I get the follo=
wing 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 f=
ails because the flag has not been set by the BEFORE function. Just to re-i=
terate - the BFORE function is very simple - it cannot exit the function wi=
thout setting the flag I have mentioned.
Have I misunderstood trigger functions or is this a legitimate bug?
Regards
Donald Fraser