[SQL] trigger before delete question

[Sigrid Thijs]

Hello,

I've bumped into a problem with a trigger before delete that I do not
immediately understand. I have made a simple example that also illustrates
the issue, but in reality the database structure is more complex.

Here's the database scheme:

create table club (
  club_id             serial not null primary key,
  club_name           text not null
);

create table member (
  member_id           serial not null primary key,
  member_name         text not null,
  club_id             integer not null references club(club_id) on delete
cascade on update cascade
);

alter table club add column contact_member_id integer references
member(member_id) on delete set null on update cascade;

create or replace function club_trigger_0() returns trigger as $$
  begin
    delete from member where club_id = old.club_id;
    return old;
  end;
$$ language plpgsql;

create trigger club_trigger_0 before delete on club for each row execute
procedure club_trigger_0();

It consists of a table club, which can have members assigned to it. A club
can also (but doesn't need to) have a contact
member. When it does, this is set in the contact_member_id field of the
club.
Now when I want to delete a club, all the members also have to be deleted. I
know this can be achieved with the 'on delete cascade', but I have a more
complex situation where some data needs to be deleted from other tables
before a member is deleted, and some data after a member has been deleted.
That's why a separate trigger is written to perform some operations before a
row is deleted from the club table.

Now, when I want to delete a club, and the contact_member_id field is null
for that row, the delete goes fine.
You can try this with the following sql:
-- create a new club
insert into club (club_name) values('club1');
-- create a new member for the new club
insert into member (member_name, club_id) values('member1', (select currval
('club_club_id_seq')));

-- delete the club with all it's members
delete from club where club_id = (select currval ('club_club_id_seq'));
select * from club;

But when a club has a contact_member_id value, all the operations in the
trigger functions are performed correctly, but the delete of the row itself
is not executed. You can try this with the following sql:

-- create a new club
insert into club (club_name) values('club2');
-- create a new member for the new club
insert into member (member_name, club_id) values('member2', (select currval
('club_club_id_seq')));
-- make the new member the contact member of the club
update club set contact_member_id = (select currval('member_member_id_seq'))
from member;

-- delete the club with all it's members
delete from club where club_id = (select currval ('club_club_id_seq'));
select * from club;

The last select statement will still return the row that should be deleted,
with the only difference that the
contact_member_id is set to null.
Is this what I should expect from plpgsql? If the subject to be deleted has
been modified during the before trigger, the delete operation will not be
executed? Is there a way to make sure the row will be deleted?

kind regards,

Sigrid