MS Access 2003
ODBC 7.03.02.09 snapshot or 7.03.02 released version
7.4.3 (cygwin)
My application needs updatable views to use as record sources for forms,
subforms and combo boxes in Access.
All are against single tables thus far, in a prototype developed with
MSDE/SQL Server. I am attempting to convert the prototype to postgresql.
Have set up a test table and view, along with INSERT, UPDATE and DELETE
rules analogous to those in the examples in section 34.3.2 of the docs. The
INSERT rule uses a nextval (sequence) call to generate the primary key
field, using the same sequence used by the underlying table.
The INSERT rule syntax is very clearcut, with the rule substituting the
underlying table for the view.
The UPDATE and DELETE examples in the docs appear to use a qualification for
the current primary key value of the record(s) to be updated or deleted:
table.column_primary_key = old.table.column_primary_key.
Why is the rule qualification needed, rather than just using the
qualification inherited from the UPDATE or DELETE query written against the
view?
The rules appear to work correctly; dropping the rule qualification results
in the UPDATE or DELETE being applied to all records in the table, ignoring
the qualification(s) from the query written against the view.
Are "old" and "new" temporary tables as with triggers, with "old" containing
the set of records that meets the qualification(s) from the query written
against the view, prior to application of the rule? That would limit the
UPDATE or DELETE rule to the desired records, which appears to be the case.
Here is the test table, view and rules that seem to work with Access:
CREATE TABLE public.tbltest
(
id serial NOT NULL,
lastnm varchar,
firstnm varchar
) WITH OIDS;
CREATE SEQUENCE public.tbltest_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 37
CACHE 1;
CREATE OR REPLACE VIEW public.vwtest AS
SELECT tbltest.id, tbltest.lastnm, tbltest.firstnm
FROM tbltest;
CREATE OR REPLACE RULE test_ins AS
ON INSERT TO vwtest DO INSTEAD INSERT INTO tbltest (id, lastnm,
firstnm)
VALUES (nextval('tbltest_id_seq'::text)::integer, new.lastnm,
new.firstnm);
CREATE OR REPLACE RULE test_upd AS
ON UPDATE TO vwtest DO INSTEAD UPDATE tbltest SET lastnm = new.lastnm,
firstnm = new.firstnm
WHERE tbltest.id = old.id;
CREATE OR REPLACE RULE test_del AS
ON DELETE TO vwtest DO INSTEAD DELETE FROM tbltest
WHERE tbltest.id = old.id;
Thanks,
David P. Lurie
