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.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
