Re: My MS-Access problem keeps getting weirder and weirder...

I think of the activity you described as TRIGGER events, more than rules.
It sound a bit complicated, like you have to avoid a series of foreign 
key violations or loops or FOREIGNS where the PRIMARY side is NOT 
transacted yet.

This situation still sound like a DATA problem with a value exceeding 
the BOUNDS of what a field will hold.

Have you tried DEBUGGING with a simplistic version where each action is 
done as separate steps?  (I suppose if the rule are in place you have to 
redo you database a bit to test this.) .... I guess you have,...that's 
what your message says.

Is it possible to use the client to send each step as a statement, all 
wrapped in a transaction?

Is it possible to write a function (stored procedure) to take care of 
the combination of steps?



Peter Bense wrote:
> I want to thank everyone who has provided suggestions regarding my
> MS-Access / ODBC / Link-tables issue the past couple of days.
>
> Here's what I've found:
>
> 1. As my gut instincts had told me, there is no problem with the
> translations of booleans, at least given how I have been using them
> (with foreign-key lookups).
>
> 2. Inserts into the following view work cleanly:
>
> 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;
>
> 3. THINGS BREAK WHEN I APPLY CERTAIN TYPES OF RULES TO THE VIEW.
>
> The way this view is supposed to work is as follows:
> A) - A data entry person enters participant ID, survey type, date
> received, etc.
> B) - A RULE performs the following insertion:
> 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());
>
> C) - A series of checks are conducted to ensure that this data is
> valid.  Basically 4 or 5 updates are run to toggle these boolean fields
> on the PostgreSQL side.  If the participant ID is a valid participant
> ID, that field is toggled.  Once it passes that field, the month and
> year of birth are verified.  If that check is successful, then it checks
> to see whether or not there is an associated tracking record already. 
> If there isn't, it passes the tracking check.
>
> All of those steps work fine on my test inserts, and the datavalues are
> toggled accordingly.  So far so good.
>
> WHEN I TRY TO INSERT DATA INTO SOME OTHER TABLE FROM THAT RULE, THINGS
> BREAK!
>
> As soon as I add an INSERT statement to my rule following the UPDATEs,
> e.g. 
> INSERT INTO tblpis_tracking (ppt_id, pre_rc_date, pre_rc_id,
> pre_is_blank)
>         SELECT ppt_id, date_received, staff_id, is_blank
>         FROM tblpis_survey_receipt
>         WHERE ppt_id=new.ppt_id
>         AND survey_type=1
>         AND check_ppt='t'
>         AND check_dob='t'
>         AND check_tracking='t';
> );
>
> ... Things break upon insert.
>
> Why?
>
> Can I only perform one insert per AS ON INSERT TO?
>
> If so, this might be the cause of my problem.
>
>
> Help!
>
> ./Peter
>
> Peter T. Bense - Teradata Certified Professional
> (ptbense@xxxxxxxxxx) - 803-777-9476 
> Database Administrator/Webmaster
> Prevention Research Center
> University of South Carolina
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)

greg.campbell.vcf
Description: Vcard

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq