Thanks for the input. I did want this process to be a batch job, but they
insisted it be 'real-time' so we came to the conclusion that a trigger would
be the best way to do it. Unfortunately there's a high potential for alot of
errors similar to the one you mentioned you had
One more question to follow-up on #2... Is it possible to delete the record
being inserted in the code for the Insert Trigger itself? I've had trouble
doing this but if it is possible I'll play with it more
--
Luch
replace nospam with usa for e-mail.
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:truiepk5qc61d9_at_news.demon.nl...
>
> "Luch" <optimaljedi_at_nospam.net> wrote in message
> news:ddHv7.7757$2p1.775996_at_newsread2.prod.itd.earthlink.net...
> > I'm new to using PLSQL, triggers
> >
> > I'd like advice on what the 'standard' way of using triggers is in the
> > following situation. We have an exchange table which is automatically
> > populated by a customer. We want to have a trigger on that table that
for
> > every record inserted by them we insert or update the necessary data to
> our
> > correspending table (both tables in same database). I'd like to hear
from
> > someone experienced in doing something similar what is the best way to
> > handle some things:
> >
> > 1) If the data can't be put into ours because it fails our bussiness
rules
> > check, how do we indicate this to the customer? Should we write the
> details
> > of the error to a log file that they should periodically check? (keep in
> > mind this should optimally be an automatic process that requires no user
> > interaction). Or should we mark a field on the exchange table to
indicate
> > that that record failed, and have them check the exchange table
> > periodically?
> >
> > 2) Is it a good idea to delete the record from the exchange table after
it
> > is successfully processed or leave it there permanently. (my original
idea
> > was to delete it until I ran into 'mutating table' errors.)
> >
> > If there's any other advice I'd appreciate it. Learning the syntax rules
> for
> > PLSQL isn't hard (plenty of websites out there to teach that), but we're
> new
> > to the actual concepts of how it should be used.
> >
> > --
> > Luch
> > replace nospam with usa for e-mail.
> >
> >
>
> 1)
> I wouldn't use a trigger for this purpose. I would write a pl/sql job
which
> runs periodically to process the exchange table. This would at least put
> less stress on your system provided your system is a production database
> attached to a website (I'm currrently in a similar situation, I didn't
write
> the interface procedures though I'm troubleshooting them and trying to
> improve on them)
>
> In my case several tables have to be kept in sync. I will concentrate on
the
> orderlines and products tables, trying to demonstrate which type of
problems
> I run into, and you can run into too.
>
> The products table is synced by means of an exchange table only once a
day.
> The orderlines table (and of course the orders table also) is synced every
3
> minutes.
> This results in orders being rejected because the product is (yet)
unknown.
> The affected orders and orderlines get the status 3 (normal status is 1)
and
> are retained in the exchange table.
> The exchange table can hold only one record for a particular order, as it
> was assumed the record was going to be updated completely (all columns,
this
> still holds true) and only *once* a day.
> This means the process inserting to the interface table fails with ora-001
> as records to be inserted still exists. Apparently this process seems to
be
> an Access append query via ODBC, so the complete process fails, when only
> one ora-0001 occurs.
> The changes I am implementing now consist of
> - whenever a product is being added all orders for that product get a
status
> of 1, so they will be automatically processed
> - Invalid orders will be rejected before anything else is done. The
> procedure doesn't read order by order anymore and rejects them inside a
> loop. The procedure checks them and flags them before entering a loop.
> - The purpose of this is we will store these rejected records in a file,
and
> hopefully, email them to the responsible people.
>
> 2) I would keep a log of all mutations to the exchange table (*this* can
be
> done by means of trigger as it is straightforward) and delete the record
> from the exchange table once it has been processed *sucessfully* (I'm
saying
> this because I inherited a situation where the record was deleted
*anyway*)
>
> Hth,
>
> Sybrand Bakker, Senior Oracle DBA
>
>
>
>
Received on Thu Oct 11 2001 - 21:43:56 CDT
