Re: Are updateable view as a linked table in ms-access a bad idea?[solved]

I think I've solved my problem with connecting an update-able view as a linked table in MS Access.
I invite all to comment on flaws or suggest improvements or other <possible> solutions.

This is a test case that I am eventually going to use to model a document hierarchy. This test
case is only modeling one branch of the hierarchy.

in this model I have three tables of increasing detail:
table foo(id, a)
table bar(id, b)
table baz(id, c)

Relationship between tables is:
foo <-(0,1) to (1,1)-> bar <-(0,1) to (1,1)-> baz

Here is how I created a working update-able view that PREFORMS UPDATES CONSISTENTLY WITHOUT
FAILURES so far.

Table, View, Rule Definitions:
-------------------------------------

create sequence public.foo_seq;

create table public.foo
(
id integer primary key
default nextval('public.foo_seq'),
a varchar(5)
);

alter sequence public.foo_seq owned by public.foo.id;

create table public.bar
(
id integer primary key
references public.foo(id)
on delete cascade,
b varchar(5)
);

create table public.baz
(
id integer primary key
references public.bar(id)
on delete cascade,
c varchar(5)
);

create view public.vbar (ctid,id,a,b) as
select b.ctid, a.id, a.a, b.b
from foo a join bar b on (a.id = b.id);

create rule vbar_insert as on insert to public.vbar
do instead
(
insert into foo(id,a) values (default,new.a);
insert into bar(id,b) values (currval('public.foo_seq'),new.b)
);

create rule vbar_update as on update to public.vbar
do instead
(
update foo set a=new.a where id=old.id;
update bar set b=new.b where (id,ctid)=(old.id,old.ctid)
);

create rule vbar_delete as on delete to public.vbar
do instead
(
delete from foo where id = old.id
--public.bar & public.baz are delete cascade
);

create view public.vbaz (ctid,id,a,b,c) as
select b.ctid, a.id, a.a, a.b, b.c
from vbar a join baz b on (a.id = b.id);

create rule vbaz_insert as on insert to public.vbaz
do instead
(
insert into vbar(a,b) values (new.a,new.b);
insert into baz(id,c) values (currval('public.foo_seq'),new.c)
);

create rule vbaz_update as on update to public.vbaz
do instead
(
update vbar set a=new.a, b=new.b where id = old.id;
update baz set c=new.c where (id,ctid)=(old.id,old.ctid)
);

create rule vbaz_delete as on delete to public.vbaz
do instead
(
delete from foo where id = old.id
--public.bar & public.baz are delete cascade
);

Regards,

Richard Broersma Jr.