Re: Procedure for Triggers

"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 Sat Oct 06 2001 - 12:59:58 CDT