|
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.
|
