mysql - 必须声明“RAISERROR”

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;
/  

我正在尝试创建一个触发器，当我尝试将新条目插入 itemtype 且 itemtype.PRICE 列值大于 4 时，该触发器会引发错误乘以当前桌面上的低价商品。

当我尝试创建触发器时，出现这些编译错误。

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

我也尝试过

    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;
    /

它符合要求，但是当我尝试将新条目插入表中时，我收到这些错误，说我的触发器失败。

    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'

Answer 1

尝试在触发器中使用 PRAGMA AUTONOMOUS_TRANSACTION。

看看我对这个问题的回答: SQL trigger on delete mutating table