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
