Обсуждение: Re: Are updateable view as a linked table in ms-access a bad idea?
> Please show us your exact view, table and rule definitions > used by this example. > > ^^^^^^^^ <-- update 0 is false > I guess what you are seeing are "partial updates" of the view > caused by a multi-action rule which doesn't see the updated > tuple in its subsequent actions anymore. This happens if you try > to update the referencing key field of a parent table which doesn't > get propagated to the joined tables, for example. Again, provide > your object definitions and we could say more (didn't find the > vwife view and its update rules by following your provided links). Here are the table,view, and update rule definitions: <thanks for the help> CREATE SEQUENCE public.person_seq INCREMENT BY 1 START WITH 1; CREATE TABLE public.person ( id integer primary key not null default nextval('public.person_seq'), name varchar(30) unique not null ); ALTER SEQUENCE public.person_seq OWNED BY public.person.id; CREATE TABLE public.wife ( id integer primary key references person(id), dresssize integer not null ); CREATE OR REPLACE VIEW public.vwife (id, name, dresssize) AS SELECT A.id, A.name, B.dresssize FROM public.person as A INNER JOIN public.wife as B ON A.id = B.ID; CREATE OR REPLACE RULE vwife_update AS ON UPDATE TO public.vwife DO INSTEAD ( UPDATE public.person SET name = NEW.name WHERE id = OLD.id; UPDATE public.wife SET dresssize = NEW.dresssize WHERE id = OLD.id ); Thanks for the consideration :-) Regards, Richard Broersma Jr.
Re: Are updateable view as a linked table in ms-access a bad idea?[solved]
От
Richard Broersma Jr
Дата:
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.