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

[Richard Broersma Jr <rabroersma@xxxxxxxxx>]

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.