trigger before delete question - Mailing list pgsql-sql

Hello,<br /><br />I've bumped into a problem with a trigger before delete that I do not immediately understand. I have
madea simple example that also illustrates the issue, but in reality the database structure is more complex.<br /><br
/>Here'sthe database scheme:<br /><br />create table club (<br />  club_id             serial not null primary key,<br
/> club_name           text not null<br />);<br /><br />create table member (<br />  member_id           serial not
nullprimary key,<br />   member_name         text not null,<br />  club_id             integer not null references
club(club_id)on delete cascade on update cascade<br />);<br /><br />alter table club add column contact_member_id
integerreferences member(member_id) on delete set null on update cascade;<br /><br />create or replace function
club_trigger_0()returns trigger as $$<br />  begin<br />    delete from member where club_id = old.club_id;<br />   
returnold;<br />  end;<br />$$ language plpgsql;<br /><br />create trigger club_trigger_0 before delete on club for
eachrow execute procedure club_trigger_0();<br /><br />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<br />member. When it does, this is set in the contact_member_id
fieldof the club.<br />Now when I want to delete a club, all the members also have to be deleted. I know this can be
achievedwith the 'on delete cascade', but I have a more complex situation where some data needs to be deleted from
othertables before a member is deleted, and some data after a member has been deleted. That's why a separate trigger is
writtento perform some operations before a row is deleted from the club table.<br /><br />Now, when I want to delete a
club,and the contact_member_id field is null for that row, the delete goes fine.<br />You can try this with the
followingsql:<br />-- create a new club<br />insert into club (club_name) values('club1');<br /> -- create a new member
forthe new club<br />insert into member (member_name, club_id) values('member1', (select currval
('club_club_id_seq')));<br/><br />-- delete the club with all it's members<br />delete from club where club_id =
(selectcurrval ('club_club_id_seq'));<br /> select * from club;<br /><br />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:<br /><br />-- create a new club<br />insert into club (club_name)
values('club2');<br/>-- create a new member for the new club<br />insert into member (member_name, club_id)
values('member2',(select currval ('club_club_id_seq')));<br /> -- make the new member the contact member of the club<br
/>updateclub set contact_member_id = (select currval('member_member_id_seq')) from member;<br /><br />-- delete the
clubwith all it's members<br />delete from club where club_id = (select currval ('club_club_id_seq'));<br /> select *
fromclub;<br /><br />The last select statement will still return the row that should be deleted, with the only
differencethat the<br />contact_member_id is set to null.<br />Is this what I should expect from plpgsql? If the
subjectto be deleted has been modified during the before trigger, the delete operation will not be executed? Is there a
wayto make sure the row will be deleted?<br /><br />kind regards,<br /><br />Sigrid<br />