Rule problem

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.

Responses

Browse pgsql-sql by date

  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