Unknown error during execution of trigger

Question

I've got an insert/update trigger set up which prevents an employee from existing in two tables at the same time. It works fine with catching illegal insertions/updates but i'm also getting another error report when testing the trigger with illegal insertions/updates.

Here's my code:

CREATE OR REPLACE TRIGGER check_foobar
BEFORE INSERT OR UPDATE OF VarX ON FOOBAR
FOR EACH ROW
DECLARE
    counter NUMBER(38);
BEGIN  
    SELECT count(*)
    INTO counter
    FROM BARFOO
    WHERE VarX = :NEW.VarX
    GROUP BY VarX;

IF counter > 0 THEN 
    RAISE_APPLICATION_ERROR(-20001, 'This is an illegal insertion/update');
    END IF;

EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('TEST');

END;
/

And the errors ORA-06512 and ORA-04088 i'm not sure of:

SQL> INSERT INTO DRIVER VALUES(2,  10345, 'AVAILABLE');

Error starting at line : 26 File @test.sql
In command -
INSERT INTO FOOBAR VALUES(2)
Error report -
ORA-20001: This is an illegal insertion/update
ORA-06512: at "HR.CHECK_FOOBAR", line 11
ORA-04088: error during execution of trigger 'HR.CHECK_FOOBAR'

When i add an exception handler for the select statement my trigger stops working properly and the illegal insertion isn't prevented. But the execution error is prevented.

UPDATE: I've added a group by and an exception to the trigger, so now the trigger still works with an exception handler but the errors ORA-06512 and ORA-04088 are still coming up with the illegal insertion/update.

Line 11 mentioned in the error is

GROUP BY VarX;

Any advice would be much appreciated.

Jeffrey Kemp · Accepted Answer · 2018-08-28 02:22:58Z

3

There is no problem here, it is working as expected.

Error starting at line : 26 File @test.sql
In command -
INSERT INTO FOOBAR VALUES(2)

This is referring to the line in your script where you have the INSERT statement. It's telling you an exception was raised at this point.

Error report -

This is showing the error stack:

ORA-20001: This is an illegal insertion/update

This is the actual exception that was raised.

ORA-06512: at "HR.CHECK_FOOBAR", line 11
ORA-04088: error during execution of trigger 'HR.CHECK_FOOBAR'

These are additional messages just to tell you where the exception was originally raised, in this case, in your trigger on line 11, where the RAISE_APPLICATION_ERROR is, as you might expect. Note that line numbers for triggers refer to the executable portion of the trigger, so in your case the DECLARE is line 1.

answered Aug 28, 2018 at 2:22

Jeffrey Kemp

60.5k15 gold badges111 silver badges161 bronze badges

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

Comments

Shaun Peterson · Accepted Answer · 2018-08-28 03:34:37Z

-1

The ORA-04088 error means that the trigger has an un-handled exception. You are raising a application error but then not handling it. You need to handle this exception as per below.

CREATE OR REPLACE TRIGGER check_foobar
BEFORE INSERT OR UPDATE OF VarX ON FOOBAR
FOR EACH ROW
DECLARE
    counter NUMBER(38);
    BEGIN  
       SELECT count(*)
       INTO counter
       FROM BARFOO
       WHERE VarX = :NEW.VarX
       GROUP BY VarX;

    IF counter > 0 THEN 
       RAISE_APPLICATION_ERROR(-20001, 'This is an illegal insertion/update');
    END IF;

EXCEPTION 
   WHEN NO_DATA_FOUND THEN 
      DBMS_OUTPUT.PUT_LINE('TEST');
   WHEN OTHERS THEN
      IF SQLCODE = -20001 THEN
        -- do some logging
        RAISE;
      ELSE
        -- do some logging and any other actions you feel are needed.   Then depending on needs you can raise or not.

      END IF;
END;
/

edited Aug 28, 2018 at 3:34

answered Aug 28, 2018 at 1:32

Shaun Peterson

1,7901 gold badge16 silver badges21 bronze badges

2 Comments

Jeffrey Kemp Over a year ago

It would be pointless to handle this exception; it would undo the purpose of the exception which is to stop the insert or update. Also, you can't have two EXCEPTION sections in the same block.

Shaun Peterson Over a year ago

Sorry that was a typo, fixed the issue, so that it is not two exception sections just multiple catches. catching the exception and explicitly raising it should stop the un handled exception appearing as you are handling and raising it but will still stop the record being inserted.

Collectives™ on Stack Overflow

Unknown error during execution of trigger

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related