From: | Svenn Helge Grindhaug <svenn(at)ii(dot)uib(dot)no> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Rule problem |
Date: | 2002-06-13 10:11:37 |
Message-ID: | Pine.SOL.4.44.0206131208300.15638-100000@buksbom.ii.uib.no |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi,
I have defined the following 3 tables (a,b,c) and 2 rules (a_delete,
c_delete) in postgres 7.2:
Table "a"
Column | Type | Modifiers
--------+---------+-----------
id1 | integer | not null
Primary key: a_pkey
Rules: a_delete
Table "b"
Column | Type | Modifiers
--------+---------+-----------
id1 | integer |
id2 | integer |
Table "c"
Column | Type | Modifiers
--------+---------+-----------
id2 | integer | not null
Primary key: c_pkey
Rules: c_delete
create rule a_delete as
on delete to a do
delete from b where id1 = old.id1;
create rule c_delete as
on delete to c do
delete from a where id1 = (select id1 from b where id2 = old.id2);
The tables contains the following values:
select * from c;
id2
-----
104
105
106
select * from a;
id1
-----
1
2
3
select * from b;
id1 | id2
-----+-----
1 | 105
The problem is that when I try to delete an entry i table c which have a
reference in table b to table a, the rule deletes the entry in table c and
table b but NOT in table a. (see output below)
Can someone please tell me why this happens and how to solve it
delete from c where id2 = 105;
DELETE 1
select * from a;
id1
-----
1 <- This should also be deleted by the c_delete rule!!!
2
3
select * from b;
id1 | id2
-----+-----
(0 rows)
select * from c;
id2
-----
104
106
Thanks
Svenn Grindhaug.
From | Date | Subject | |
---|---|---|---|
Next Message | SDX | 2002-06-13 10:51:40 | How to unsubscribe? |
Previous Message | Richard Huxton | 2002-06-13 08:50:45 | Re: General trigger function |