DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=YZVIcQz28KuhXYsdwwNYEzxT+AsIP+AzudSCk+iiBMYLxTesXIN69aaAxLxySmiiwa
	RDxgBWJ4u32paq2GzaitsOxqQ07Gb4cYxmswuF7QPLhrhXCHO1p+dzt7nthwOdiTOHGk
	1zEzp9QJ/dPTeRGstL+VTjuF4lz3h+dW+YJJs=
MIME-Version: 1.0
Date: Tue, 21 Apr 2009 14:03:50 +0200
Message-ID: <23f7d19d0904210503h4eaaef70q96c4338d71f73fee@mail.gmail.com>
Subject: trigger before delete question
From: Sigrid Thijs <sigrid.thijs@gmail.com>
To: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=000e0cd2981aa86e4204680f704b

--000e0cd2981aa86e4204680f704b
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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

--000e0cd2981aa86e4204680f704b
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hello,<br><br>I&#39;ve bumped into a problem with a trigger before delete t=
hat I do not immediately understand. I have made a simple example that also=
 illustrates the issue, but in reality the database structure is more compl=
ex.<br>
<br>Here&#39;s the database scheme:<br><br>create table club (<br>=A0 club_=
id=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 serial not null primary key,<br>=A0 =
club_name=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 text not null<br>);<br><br>create t=
able member (<br>=A0 member_id=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 serial not nul=
l primary key,<br>
=A0 member_name=A0=A0=A0=A0=A0=A0=A0=A0 text not null,<br>=A0 club_id=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 integer not null references club(club_id) on=
 delete cascade on update cascade<br>);<br><br>alter table club add column =
contact_member_id integer references member(member_id) on delete set null o=
n update cascade;<br>
<br>create or replace function club_trigger_0() returns trigger as $$<br>=
=A0 begin<br>=A0=A0=A0 delete from member where club_id =3D old.club_id;<br=
>=A0=A0=A0 return old;<br>=A0 end;<br>$$ language plpgsql;<br><br>create tr=
igger club_trigger_0 before delete on club for each row execute procedure c=
lub_trigger_0();<br>
<br>It consists of a table club, which can have members assigned to it. A c=
lub can also (but doesn&#39;t need to) have a contact<br>member. When it do=
es, this is set in the contact_member_id field of the club.<br>Now when I w=
ant to delete a club, all the members also have to be deleted. I know this =
can be achieved with the &#39;on delete cascade&#39;, but I have a more com=
plex 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&#=
39;s why a separate trigger is written to 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 n=
ull for that row, the delete goes fine.<br>You can try this with the follow=
ing sql:<br>-- create a new club<br>insert into club (club_name) values(=
9;club1&#39;);<br>
-- create a new member for the new club<br>insert into member (member_name,=
 club_id) values(&#39;member1&#39;, (select currval (&#39;club_club_id_seq&=
#39;)));<br><br>-- delete the club with all it&#39;s members<br>delete from=
 club where club_id =3D (select currval (&#39;club_club_id_seq&#39;));<br>
select * from club;<br><br>But when a club has a contact_member_id value, a=
ll 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 follow=
ing sql:<br>
<br>-- create a new club<br>insert into club (club_name) values(&#39;club2&=
#39;);<br>-- create a new member for the new club<br>insert into member (me=
mber_name, club_id) values(&#39;member2&#39;, (select currval (&#39;club_cl=
ub_id_seq&#39;)));<br>
-- make the new member the contact member of the club<br>update club set co=
ntact_member_id =3D (select currval(&#39;member_member_id_seq&#39;)) from m=
ember;<br><br>-- delete the club with all it&#39;s members<br>delete from c=
lub where club_id =3D (select currval (&#39;club_club_id_seq&#39;));<br>
select * from club;<br><br>The last select statement will still return the =
row that should be deleted, with the only difference that the<br>contact_me=
mber_id is set to null.<br>Is this what I should expect from plpgsql? If th=
e subject to be deleted has been modified during the before trigger, the de=
lete operation will not be executed? Is there a way to make sure the row wi=
ll be deleted?<br>
<br>kind regards,<br><br>Sigrid<br>

--000e0cd2981aa86e4204680f704b--