Re: deleting from a view

On Tue, 17 Oct 2000, shawn everett wrote:

> Before I get flamed for not RTFM I do know I can use a rule to do this.
>
> Here's the problem:
>
> I have a view that is made up of two tables.
>
> When I delete from the view I would like the records to be deleted from
> both tables that make up my view. What is the best way to accomplish
> this?
>
> Should I use a trigger or a rule? If I use a trigger how should it be
> written. If I use a rule how do I put two delete statements in it.
>
> I'd like to be able to do something along the lines of:
>
> CREATE RULE view_raw_reman_prod_delete3 AS
> ON DELETE TO view_raw_reman_prod
> DO INSTEAD
> 'BEGIN
> DELETE FROM reman_costraw1 WHERE prod_date=old.prod_date;
> DELETE FROM reman_cost_total_raw1 WHERE prod_date=old.prod_date;
> END;';
>
> This doesn't work :)

IIRC, the correct syntax for multiple actions is
CREATE RULE ...
DO INSTEAD (
<action>;
<action>;
);

(My test set was three tables a b and c, each having a column named a
which was an int, and my rule was
create rule rule as on delete to a do instead (
delete from b where a=OLD.a; delete from c where a=OLD.a);
which appeared to work in my minimal test (delete a row from
a and see what happens)