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

[Richard Broersma Jr]

> 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.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend