Re: My MS-Access problem keeps getting weirder and

--- Peter Bense <Ptbense(at)gwm(dot)sc(dot)edu> wrote:

> This is what I have been reflecting on for awhile.
> I spoke to some individuals in #postgresql about the
> possibility of using a 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? As I understand it a rule is a type of
> procedure in and of itself. Is this understanding
> correct? If so, what would prevent me from being
> able to execute more than one INSERT per rule?

AFAIK you can. But I don't mess with rules, and
reading posts like yours doesn't encourage me to try.
:-) Rules have a way of seeming simpler than they
are, and lots of people get tripped up.

You may be better off keeping the rule just for the
initial insert, then doing your other operations in an
after insert trigger on the main table.

>
> ./Peter
>
> 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
>
> >>> "Greg Campbell" <greg(dot)campbell(at)us(dot)michelin(dot)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.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose
> an index scan if your
> joining column's datatypes do not match
>

__________________________________
Do you Yahoo!?
Make Yahoo! your home page
http://www.yahoo.com/r/hs