pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtrigger before delete question
2+ messages / 2 participants
[nested] [flat]
* trigger before delete question
@ 2009-04-21 12:03 Sigrid Thijs <sigrid.thijs@gmail.com>
2009-04-21 14:13 ` Re: trigger before delete question Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Sigrid Thijs @ 2009-04-21 12:03 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: trigger before delete question
2009-04-21 12:03 trigger before delete question Sigrid Thijs <sigrid.thijs@gmail.com>
@ 2009-04-21 14:13 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2009-04-21 14:13 UTC (permalink / raw)
To: Sigrid Thijs <sigrid.thijs@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2009-04-21 14:13 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-04-21 12:03 trigger before delete question Sigrid Thijs <sigrid.thijs@gmail.com>
2009-04-21 14:13 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox