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
-----104105106
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
-----104106
Thanks
Svenn Grindhaug.