Re: My MS-Access problem keeps getting weirder and weirder...
От | Greg Campbell |
---|---|
Тема | Re: My MS-Access problem keeps getting weirder and weirder... |
Дата | |
Msg-id | 428A60FE.8090708@us.michelin.com обсуждение исходный текст |
Ответ на | My MS-Access problem keeps getting weirder and weirder... ("Peter Bense" <Ptbense@gwm.sc.edu>) |
Список | pgsql-odbc |
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@gwm.sc.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@postgresql.org)
Вложения
В списке pgsql-odbc по дате отправления:
Предыдущее
От: "Peter Bense"Дата:
Сообщение: My MS-Access problem keeps getting weirder and weirder...