ORACLE PL/SQL Programming problems with trigger

Question

Hi I got a problem running a trigger created using ORACLE PL/SQL programming. Basically, whenever there is an insertion with a location that does not exist in the database, I have to insert a new tuple into FEATURES table. Here's the Trigger -

CREATE OR REPLACE TRIGGER sightTrigger
  AFTER INSERT
  ON SIGHTINGS
  FOR EACH ROW
DECLARE
  x INTEGER;
BEGIN
  SELECT COUNT(*)INTO x
    FROM FEATURES
    WHERE FEATURES.location = :NEW.location;

  IF(x=0) THEN
    RAISE APPLICATION ERROR(-20001, 'Warning: Insert into the SIGHTINGS    ...');

    INSERT INTO FEATUERS(LOCATION,CLASS,LATITUDE<LONGITUDE,MAP,ELEV)
      VALUES(:NEW.location, 'UNKNOWN', null, null, null, null);
  END IF;
END sightTrigger;

It compiled fine but I ran a simple query to test it -

INSERT INTO SIGHTINGS VALUES ('Douglas dustymaiden', 'Person A', 'Piute', TO_DATE('17Feb07', 'DDMONYY'));

And it gave me an error called - "ORA-20001: Warning: Insert into the SIGHTINGS..." (What I wanted it) "ORA-06512: at line 7" "ORA-04088: error during execution of trigger"

Then the insertion into the FEATURES table didn't occur when I tested it. Please help.

Shadow Shadow · Accepted Answer · 2016-04-22T23:29:39

As per documentation on raise_application_error:

When called, raise_application_error ends the subprogram and returns a user-defined error number and message to the application. The error number and message can be trapped like any Oracle error.

So, first do the other insert, then raise the error. However, if you raise an unhandled error in a trigger, that will roll back the entire transaction and any data modifications with it and the trigger will error. You may want to consider returning a message to the user in a different way.

ORACLE PL/SQL Programming problems with trigger

1 Answers