Обсуждение: trigger before delete question

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 /> 

Sigrid Thijs <sigrid.thijs@gmail.com> writes:
> 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.

The reason it doesn't work is that the delete from member cascades back
to update the club row (ie, set contact_member_id to null) and so by the
time the original delete is attempted the row version it's against is
already obsolete.  That results in nothing happening, not in re-issuing
the delete against the updated row version.

Personally I'd recommend rethinking this unholy mix of recursive foreign
keys and bad manual substitutes for foreign keys.  However, if you can't
come up with a less bogus schema design, you might find that it helps to
propagate information to derived rows in AFTER triggers rather than
BEFORE triggers.
        regards, tom lane