I've got this funky problem. Basically I have a table that contains:
afl=# \d tblpis_survey_receipt Table
"public.tblpis_survey_receipt" Column | Type |
Modifiers
----------------+--------------------------------+------------------------------------------------------------------------------insertion
| integer | not null default
nextval('public.tblpis_survey_receipt_insertion_seq'::text)ppt_id | integer | not
nulldate_received | date | not nullstaff_id | integer | not
nullsurvey_type | smallint | not nullis_blank | boolean | not
nullbirth_month | smallint |birth_year | smallint |check_ppt |
boolean | not nullcheck_dob | boolean | not nullcheck_tracking |
boolean | not nulldate_inserted | timestamp(0) without time zone | not nulldate_modified |
timestamp(0)without time zone | not null
The goal of this table is to provide a location for staff members to
enter information relevant to the tracking of participant surveys.
They'll have a form where they'll enter this basic data:
ppt_id, date_received, survey_type, is_blank, birth_month, birth_year.
THEN [the part where I'd need the rule thing working] what should
happen is this.
The above 6 fields are entered into a view, which then inserts 'f'
values for the "check" fields by default into the table.
Then [also as part of the rule attached to the view] we perform
checks:
1. check_ppt looks to the participant table to make sure the ppt_id is
valid. If valid, then:
2. check_dob occurs, which verifies that the month and year of birth
entered by the data entry person matches the respective fields in the
parent record. Simple enough.
3. check_tracking looks to the tracking table to ensure that there
hasn't already been a record created or field populated for that type of
survey in that participant's tracking record. If this is okay, then
[and this is the part where things get weird]:
A) We should insert records into the tracking table where there isn't
one already.
B) We should update tracking records where a record exists but there
isn't an entry for that type of survey date received.
Everything works fine until I get to A & B. If I enter these as
inserts via psql, they work fine.
Problem is, I am using MS-Access, which returns some really weird-ass
error message about the data entered being too large for the field... if
I include more than one `INSERT` in the rule?
Here's my rule [hopefully someone can help advise of a more elegant way
to do this?]
CREATE OR REPLACE RULE 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, 'f', 'f', 'f', now(),
now());
UPDATE tblpis_survey_receipt SET check_ppt='t'WHERE tblpis_survey_receipt.ppt_id IN (SELECT ppt_id FROM
tblpis_participant);
UPDATE tblpis_survey_receipt SET check_dob='t'WHERE tblpis_survey_receipt.ppt_id=new.ppt_idAND
tblpis_survey_receipt.check_ppt='t'ANDtblpis_survey_receipt.ppt_id IN (select 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='t'WHERE tblpis_survey_receipt.ppt_id NOT IN (SELECT ppt_id from
tblpis_tracking);
UPDATE tblpis_survey_receipt SET check_tracking='t'WHERE tblpis_survey_receipt.survey_type='1' AND
tblpis_survey_receipt.ppt_id NOT IN (SELECT ppt_id from tblpis_tracking where pre_rc_date IS
NOT NULL);
UPDATE tblpis_survey_receipt SET check_tracking='t'WHERE tblpis_survey_receipt.survey_type='2' AND
tblpis_survey_receipt.ppt_id NOT IN (SELECT ppt_id from tblpis_tracking where post_rc_date
IS NOT NULL);
INSERT INTO tblpis_tracking (ppt_id, pre_rc_date, pre_rc_id,
pre_is_blank)SELECT ppt_id, date_received, staff_id, is_blankFROM tblpis_survey_receiptWHERE ppt_id=new.ppt_idAND
survey_type=1ANDcheck_ppt='t'AND check_dob='t'AND check_tracking='t';
INSERT INTO tblpis_tracking (ppt_id, post_rc_date, post_rc_id,
post_is_blank)SELECT ppt_id, date_received, staff_id, is_blankFROM tblpis_survey_receiptWHERE ppt_id=new.ppt_id AND
survey_type=2 AND check_ppt='t' AND check_dob='t' AND check_tracking='t' AND new.ppt_id NOT IN (select
ppt_idfrom
tblpis_tracking);
UPDATE tblpis_trackingSET post_rc_date=new.date_received, post_rc_id=new.staff_id,
post_is_blank=new.is_blankWHERE new.ppt_id IN (SELECT ppt_id FROM tblpis_survey_receipt WHERE
ppt_id=new.ppt_id AND survey_type=2 AND check_ppt='t' AND check_dob='t' AND
check_tracking='t');
);
Peter T. Bense - Teradata Certified Professional
(ptbense@gwm.sc.edu) - 803-777-9476
Database Administrator/Webmaster
Prevention Research Center
University of South Carolina
