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 · Accepted Answer · 2016-04-22 23:48:17Z

0

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.

edited Apr 22, 2016 at 23:48

answered Apr 22, 2016 at 23:29

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

tedkim20 Over a year ago

I still got an error after moving the insert part on top of raising the error

Shadow Over a year ago

Is the error message classified or do you just expect me to use my mind reading skills?

tedkim20 Over a year ago

Same error, ORA-06512: at line 9 and ORA-04088: error during execution of trigger.

tedkim20 Over a year ago

All right! I used the dbms_output.put_line instead of raising the error. Thank you so much!

Joao Polo Over a year ago

you could to create a inner transaction, before the raise (begin, commit), but you need to know if the outer transaction performs rollback, the inner insert don't be removed

Collectives™ on Stack Overflow

ORACLE PL/SQL Programming problems with trigger

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest