Обсуждение: 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.

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.