Re: Trigger fails under SQL*Loader

Ramon F Herrera wrote:
> I wrote a trigger which works as expected if the
> records are inserted in a PL/SQL statement. However,
> if the records are inserted by SQL*Loader, the
> trigger doesn't work anymore.
>
> As you can see below, the program checks first to see
> whether the unique key is already present before attempting
> the INSERT.
>
> Thanks for any insight on this,
>
> -Ramon F Herrera
>
> ------------------
> CREATE OR REPLACE TRIGGER syncSHAWS
> AFTER INSERT ON shaws
> FOR EACH ROW
>
> DECLARE
> code client.code%TYPE;
> howMany NUMBER;
> holder cache.hits%TYPE;
>
> BEGIN
>
> code := '0019';
>
> SELECT COUNT(*) INTO howMany FROM cache WHERE ssn = :new.ssn;
>
> IF (howMany = 0) THEN
>
> INSERT INTO cache VALUES (:new.ssn, code || '+');
>
> ELSE
>
> SELECT hits INTO holder FROM cache WHERE ssn = :new.ssn;
> holder := holder || code || '+';
> UPDATE cache SET ssn = :new.ssn, hits = holder;
>
> END IF;
>
> END syncSHAWS;
>
> ------------------
> Record 7: Rejected - Error on table SHAWS.
> ORA-00001: unique constraint (DATABASE.CACHE) violated
> ORA-06512: at "DATABASE.SYNCSHAWS", line 21
> ORA-04088: error during execution of trigger 'DATABASE.SYNCSHAWS'

Using Direct Path with SQL*Loader?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)