I hate MS-Access & ODBC.

Scenario 1:

Inserting via ODBC in MS-Access on the following view FAILS:

afl=# \d vi_tblpis_survey_receipt
View "public.vi_tblpis_survey_receipt"
Column | Type | Modifiers
---------------+----------+-----------
ppt_id | integer |
date_received | date |
staff_id | integer |
survey_type | smallint |
is_blank | boolean |
birth_month | smallint |
birth_year | smallint |
View definition:
SELECT tblpis_survey_receipt.ppt_id,
tblpis_survey_receipt.date_received, tblpis_survey_receipt.staff_id,
tblpis_survey_receipt.survey_type, tblpis_survey_receipt.is_blank,
tblpis_survey_receipt.birth_month, tblpis_survey_receipt.birth_year
FROM tblpis_survey_receipt
ORDER BY tblpis_survey_receipt.insertion;
Rules:
tblpis_survey_receipt_in AS
ON INSERT TO vi_tblpis_survey_receipt DO INSTEAD ( INSERT INTO
tblpis_survey_receipt (ppt_id, date_received, staff_id, survey_type,
is_blank, birth_month, birth_year, check_ppt, check_dob, check_tracking,
date_inserted, date_modified)
VALUES (new.ppt_id, new.date_received, new.staff_id, new.survey_type,
new.is_blank, new.birth_month, new.birth_year, false, false, false,
now(), now());
UPDATE tblpis_survey_receipt SET check_ppt = true
WHERE (tblpis_survey_receipt.ppt_id IN ( SELECT
tblpis_participant.ppt_id
FROM tblpis_participant));
UPDATE tblpis_survey_receipt SET check_dob = true
WHERE tblpis_survey_receipt.ppt_id = new.ppt_id AND
tblpis_survey_receipt.check_ppt = true AND (tblpis_survey_receipt.ppt_id
IN ( SELECT tblpis_participant.ppt_id
FROM tblpis_participant
WHERE tblpis_survey_receipt.ppt_id =
tblpis_participant.ppt_id AND tblpis_survey_receipt.birth_month =
tblpis_participant.birth_month AND tblpis_survey_receipt.birth_year =
tblpis_participant.birth_year));
UPDATE tblpis_survey_receipt SET check_tracking = true
WHERE NOT (tblpis_survey_receipt.ppt_id IN ( SELECT
tblpis_tracking.ppt_id
FROM tblpis_tracking));
UPDATE tblpis_survey_receipt SET check_tracking = true
WHERE tblpis_survey_receipt.survey_type = 1::smallint AND NOT
(tblpis_survey_receipt.ppt_id IN ( SELECT tblpis_tracking.ppt_id
FROM tblpis_tracking
WHERE tblpis_tracking.pre_rc_date IS NOT NULL));
UPDATE tblpis_survey_receipt SET check_tracking = true
WHERE tblpis_survey_receipt.survey_type = 2::smallint AND NOT
(tblpis_survey_receipt.ppt_id IN ( SELECT tblpis_tracking.ppt_id
FROM tblpis_tracking
WHERE tblpis_tracking.post_rc_date IS NOT NULL));
INSERT INTO tblpis_tracking (ppt_id, pre_rc_date, pre_rc_id,
pre_is_blank) SELECT tblpis_survey_receipt.ppt_id,
tblpis_survey_receipt.date_received, tblpis_survey_receipt.staff_id,
tblpis_survey_receipt.is_blank
FROM tblpis_survey_receipt
WHERE tblpis_survey_receipt.ppt_id = new.ppt_id AND
tblpis_survey_receipt.survey_type = 1 AND
tblpis_survey_receipt.check_ppt = true AND
tblpis_survey_receipt.check_dob = true AND
tblpis_survey_receipt.check_tracking = true;
INSERT INTO tblpis_tracking (ppt_id, post_rc_date, post_rc_id,
post_is_blank) SELECT tblpis_survey_receipt.ppt_id,
tblpis_survey_receipt.date_received, tblpis_survey_receipt.staff_id,
tblpis_survey_receipt.is_blank
FROM tblpis_survey_receipt
WHERE tblpis_survey_receipt.ppt_id = new.ppt_id AND
tblpis_survey_receipt.survey_type = 2 AND
tblpis_survey_receipt.check_ppt = true AND
tblpis_survey_receipt.check_dob = true AND
tblpis_survey_receipt.check_tracking = true AND NOT (new.ppt_id IN (
SELECT tblpis_tracking.ppt_id
FROM tblpis_tracking));
UPDATE tblpis_tracking SET post_rc_date = new.date_received,
post_rc_id = new.staff_id, post_is_blank = new.is_blank
WHERE (new.ppt_id IN ( SELECT tblpis_survey_receipt.ppt_id
FROM tblpis_survey_receipt
WHERE tblpis_survey_receipt.ppt_id = new.ppt_id AND
tblpis_survey_receipt.survey_type = 2 AND
tblpis_survey_receipt.check_ppt = true AND
tblpis_survey_receipt.check_dob = true AND
tblpis_survey_receipt.check_tracking = true));
)
=============================================================================
NOTE THE ERROR FROM MY psqlodbc log (why the hell does it send ROLLBACK
query types??

conn=141965240, query='INSERT INTO "public"."vi_tblpis_survey_receipt"

("ppt_id","date_received","staff_id","survey_type","is_blank","birth_month","birth_year")
VALUES (10001,'1942-12-29'::date,2,1::int2,'0',12::int2,1977::int2)'
conn=141965240, query='ROLLBACK'
conn=141965240, query='INSERT INTO "public"."vi_tblpis_survey_receipt"

("ppt_id","date_received","staff_id","survey_type","is_blank","birth_month","birth_year")
VALUES (10001,'1942-12-29'::date,2,1::int2,'0',12::int2,1977::int2)'
conn=141965240, query='ROLLBACK'
conn=141965240, query='INSERT INTO "public"."vi_tblpis_survey_receipt"

("ppt_id","date_received","staff_id","survey_type","is_blank","birth_month","birth_year")
VALUES (10001,'1942-12-29'::date,2,1::int2,'0',12::int2,1977::int2)'
conn=141965240, query='ROLLBACK'
conn=141965240, query='INSERT INTO "public"."vi_tblpis_survey_receipt"

("ppt_id","date_received","staff_id","survey_type","is_blank","birth_month","birth_year")
VALUES (10002,'2005-01-01'::date,2,2::int2,'0',8::int2,1194::int2)'
conn=141965240, query='ROLLBACK'

-- NOTE HOWEVER, a similar insert WORKS FINE FROM PSQL:

afl=# INSERT INTO "public"."vi_tblpis_survey_receipt"
("ppt_id","date_received","staff_id","survey_type","is_blank","birth_month","birth_year")
VALUES (10006,'2005-01-01'::date,2,2::int2,'0',8::int2,1194::int2);
INSERT 0 0
afl=# select * from vi_tblpis_survey_receipt ;
ppt_id | date_received | staff_id | survey_type | is_blank |
birth_month | birth_year
--------+---------------+----------+-------------+----------+-------------+------------
1 | 1977-12-29 | 2 | 2 | f |
12 | 1977
2 | 2004-05-24 | 2 | 2 | f |
12 | 1969
99 | 2005-05-16 | 2 | 1 | f |
12 | 1944
100001 | 2005-05-18 | 2 | 1 | f |
8 | 1938
100002 | 2005-05-18 | 2 | 1 | f |
10 | 1922
100002 | 2005-05-19 | 2 | 1 | f |
10 | 1922
99 | 1977-12-29 | 2 | 1 | f |
12 | 77
1000 | 1977-12-29 | 2 | 1 | f |
12 | 77
100001 | 1977-12-29 | 2 | 2 | f |
8 | 1938
10001 | 1977-12-29 | 2 | 2 | f |
8 | 1938
10002 | 2005-01-01 | 2 | 2 | f |
8 | 1194
10005 | 2005-01-01 | 2 | 2 | f |
8 | 1194
10006 | 2005-01-01 | 2 | 2 | f |
8 | 1194
(13 rows)

afl=#
=============================================================================

Scenario 2:

HOWEVER, if I change the behavior of the RULE slightly [namely, remove
the last 3 steps -- the INSERT INTO, INSERT INTO , UPDATE), it works
fine. Just to verify, here's how the rule looks with changes applied:

afl=# \d vi_tblpis_survey_receipt
View "public.vi_tblpis_survey_receipt"
Column | Type | Modifiers
---------------+----------+-----------
ppt_id | integer |
date_received | date |
staff_id | integer |
survey_type | smallint |
is_blank | boolean |
birth_month | smallint |
birth_year | smallint |

View definition:
SELECT tblpis_survey_receipt.ppt_id,
tblpis_survey_receipt.date_received, tblpis_survey_receipt.staff_id,
tblpis_survey_receipt.survey_type, tblpis_survey_receipt.is_blank,
tblpis_survey_receipt.birth_month, tblpis_survey_receipt.birth_year
FROM tblpis_survey_receipt
ORDER BY tblpis_survey_receipt.insertion;
Rules:
tblpis_survey_receipt_in AS
ON INSERT TO vi_tblpis_survey_receipt DO INSTEAD ( INSERT INTO
tblpis_survey_receipt (ppt_id, date_received, staff_id, survey_type,
is_blank, birth_month, birth_year, check_ppt, check_dob, check_tracking,
date_inserted, date_modified)
VALUES (new.ppt_id, new.date_received, new.staff_id, new.survey_type,
new.is_blank, new.birth_month, new.birth_year, false, false, false,
now(), now());
UPDATE tblpis_survey_receipt SET check_ppt = true
WHERE (tblpis_survey_receipt.ppt_id IN ( SELECT
tblpis_participant.ppt_id
FROM tblpis_participant));
UPDATE tblpis_survey_receipt SET check_dob = true
WHERE tblpis_survey_receipt.ppt_id = new.ppt_id AND
tblpis_survey_receipt.check_ppt = true AND (tblpis_survey_receipt.ppt_id
IN ( SELECT tblpis_participant.ppt_id
FROM tblpis_participant
WHERE tblpis_survey_receipt.ppt_id =
tblpis_participant.ppt_id AND tblpis_survey_receipt.birth_month =
tblpis_participant.birth_month AND tblpis_survey_receipt.birth_year =
tblpis_participant.birth_year));
UPDATE tblpis_survey_receipt SET check_tracking = true
WHERE NOT (tblpis_survey_receipt.ppt_id IN ( SELECT
tblpis_tracking.ppt_id
FROM tblpis_tracking));
UPDATE tblpis_survey_receipt SET check_tracking = true
WHERE tblpis_survey_receipt.survey_type = 1::smallint AND NOT
(tblpis_survey_receipt.ppt_id IN ( SELECT tblpis_tracking.ppt_id
FROM tblpis_tracking
WHERE tblpis_tracking.pre_rc_date IS NOT NULL));
UPDATE tblpis_survey_receipt SET check_tracking = true
WHERE tblpis_survey_receipt.survey_type = 2::smallint AND NOT
(tblpis_survey_receipt.ppt_id IN ( SELECT tblpis_tracking.ppt_id
FROM tblpis_tracking
WHERE tblpis_tracking.post_rc_date IS NOT NULL));
)

... THINGS INSERT CLEANLY! WTF? WTF?

conn=141965240, query='INSERT INTO "public"."vi_tblpis_survey_receipt"

("ppt_id","date_received","staff_id","survey_type","is_blank","birth_month","birth_year")
VALUES (10002,'2005-01-01'::date,2,2::int2,'0',8::int2,1194::int2)'
conn=141965240, query='COMMIT'
conn=141965240, query='SELECT
"ppt_id","date_received","staff_id","survey_type","is_blank","birth_month","birth_year"
FROM "public"."vi_tblpis_survey_receipt" WHERE "ppt_id" = 10002 AND
"date_received" = '2005-01-01'::date AND "survey_type" = 2::int2'
[ fetched 1 rows ]
conn=141965240, query='SELECT
"ppt_id","date_received","staff_id","survey_type","is_blank","birth_month","birth_year"
FROM "public"."vi_tblpis_survey_receipt" WHERE "ppt_id" = 10002 AND
"date_received" = '2005-01-01'::date AND "survey_type" = 2::int2'
[ fetched 1 rows ]

Peter T. Bense - Teradata Certified Professional
(ptbense(at)gwm(dot)sc(dot)edu) - 803-777-9476
Database Administrator/Webmaster
Prevention Research Center
University of South Carolina