Re: My MS-Access problem keeps getting weirder and - Mailing list pgsql-odbc

This is what I have been reflecting on for awhile.  I spoke to some individuals in #postgresql about the possibility of
usinga trigger, stored procedure, or something like that.  [Which honestly I have no experience in implementing.] 

The rules method seems to be the most straightfoward implementation, however * but perhaps that's a false presumption?
AsI understand it a rule is a type of procedure in and of itself.  Is this understanding correct?  If so, what would
preventme from being able to execute more than one INSERT per rule? 

./Peter

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

>>> "Greg Campbell" <greg.campbell@us.michelin.com> 5/17/2005 5:24:14 PM >>>
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.