Trigger fails under SQL*Loader

From: Ramon F Herrera <ramon_at_conexus.net>
Date: 14 Nov 2004 19:59:20 -0800
Message-ID: <c9bc36ff.0411141959.640362ba@posting.google.com>

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'

Received on Sun Nov 14 2004 - 21:59:20 CST