Re: SQL Loader/Trigger problem

A copy of this was sent to Allen Chow <Allen.Chow_at_CRHA-Health.Ab.Ca> (if that email address didn't require changing) On Thu, 27 May 1999 23:14:08 GMT, you wrote:

>Hi,
>
>I was wondering if any of you have come across this problem. I'm using
>SQL Loader to import records and relying on constraints (not nulls and
>referential) and triggers to kick out bad data. I would then load the
>"bad" file into another table and process those rows for any further
>errors and generate an error report to give back to the people who
>created the data so that they can fix the records. I have a "before
>insert for each row" trigger on a table and right now it seems to get
>records kicked out saying the insert failed because the parent key does
>not exist when the value actually exists in the parent table. The only
>checking I'm doing in this trigger is if a column has a certain value
>then this other column can only have this value. If I disable the
>trigger, those records are inserted with no referential errors but when
>I enable the trigger they get kicked out.
>
>Allen
>

1. lets see the trigger, the ctl file
2. why not use declaritive RI (primary key/foreign key) constraints. It'll be faster and it is really quite hard in a multi-user system to enforce parent/child relationships using a TRIGGER (there are lots of gotcha's to consider due to multi-versioning and read consistency that most people don't consider).

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri May 28 1999 - 07:17:09 CDT