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.