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
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(),
> 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
> As soon as I add an INSERT statement to my rule following the UPDATEs,
> INSERT INTO tblpis_tracking (ppt_id, pre_rc_date, pre_rc_id,
> 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.
> Can I only perform one insert per AS ON INSERT TO?
> If so, this might be the cause of my problem.
> 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
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
In response to
pgsql-odbc by date
|Next:||From: Zoltan Boszormenyi||Date: 2005-05-17 22:04:01|
|Subject: PsqlODBC problem with complex query|
|Previous:||From: Peter Bense||Date: 2005-05-17 19:14:00|
|Subject: My MS-Access problem keeps getting weirder and weirder...|