Thread: how to delete from a view

I know how to create a rule to delete from a view.  But I can’t

figure this one out.

Let’s say I have two tables, t1 and t2, and a view tview that

uses columns from both t1 and t2.  I don’t want users to

have access to t1 and t2, only to tview.  I have a rule that

handles inserts and updates to tview, but I can’t figure out

how to do the delete.  I would like a delete to tview to delete

the row from both tables.

Both t1 and t2 have a field called ‘id’.  t1 also has a field

called ‘type’.  tview is defined like

select t1.a, t1.b, t2.c, t2.d from t1, t2 where t1.type=1 and t1.id=t2.id; 

tview includes only some rows from t1 but all rows from t2.

If I create a rule like:

create rule tviewdel as on delete to view do instead (

   delete from t1 where id=old.id;

   delete from t2 where id=old.id;

);

The first delete works.  The second delete does not.

I assume that is because the row is no longer in tview

once the row is deleted from one of the underlying tables?

I’ve tried playing with triggers, but have not found the

right combination.

Any help is appreciated!  Also, please let me know

if I’m not making any sense.  It’s a little late.