'RAISERROR' must be declared

Question

CREATE TRIGGER x AFTER INSERT ON itemtype 
FOR EACH ROW
DECLARE 
    minn itemtype.PRICE%type;
BEGIN 
select MIN(itemtype.PRICE) into minn from itemtype;
IF (:new.PRICE > minn*4) then RAISERROR('Custom text');
END IF;
END;
/

I'm trying to create a trigger that raises an error when I try to insert a new entry into the itemtype with itemtype.PRICE column value is greater than 4 times the current low priced item on the table.

I get these compilation errors when I try to create the trigger.

LINE/COL ERROR
-------- --------------------------------------------------------------
5/31     PL/SQL: Statement ignored
5/31     PLS-00201: identifier 'RAISERROR' must be declared

I have also tried

    CREATE TRIGGER x AFTER INSERT ON itemtype 
    FOR EACH ROW
    DECLARE 
        minn itemtype.PRICE%type;
    BEGIN 
    select MIN(itemtype.PRICE) into minn from itemtype;
    if (:new.PRICE > minn*4) then raise_application_error(-20010,'Too Expensive');
    END IF;
    END;
    /

which complies, but when i try to insert a new entry into the table I get theses errors saying my trigger fails.

    SQL> insert into itemtype(ITEMNUM,NAME,PICTURE,PRICE,BELONGSTO ) VALUES ('A11','The who knows','',10.99,'P');
    insert into itemtype(ITEMNUM,NAME,PICTURE,PRICE,BELONGSTO ) VALUES ('A11','The who knows','',10.99,'P')
                *
    ERROR at line 1:
    ORA-04091: table USERNAME.ITEMTYPE is mutating, trigger/function may not see it
    ORA-06512: at "USERNAME.X", line 5
    ORA-04088: error during execution of trigger 'USERNAME.X'

I have also tried RAISERROR('custom text', 10, 1); and i get the same error — glassofwotah, Dec 05 '17 at 21:23
Does this help? https://stackoverflow.com/questions/6020450/oracle-pl-sql-raise-user-defined-exception-with-custom-sqlerrm — Andrei Hirsu, Dec 05 '17 at 21:43
I think you have something tagged wrong here. Your error references PL/SQL but this is tagged MySQL. Try RAISE. https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/raise_statement.htm — Jacob H, Dec 05 '17 at 21:43

score 0 · Answer 1 · answered Dec 06 '17 at 07:05

0

Try use PRAGMA AUTONOMOUS_TRANSACTION in trigger.

Look my answer on this question: SQL trigger on delete mutating table

answered Dec 06 '17 at 07:05

lukasz.kiszka

163
8

please return feedback if my answer is solution for your question – lukasz.kiszka Dec 11 '17 at 20:15

'RAISERROR' must be declared

1 Answers1