创建Oracle触发器报Statement Ignored与PLS-00405错误排查

你写的两段SELECT INTO给user_audit、user_evdnse赋值的语法本身没有错误。
你遇到的PLS-00405: 上下文不允许使用子查询、Statement Ignored两个编译错误，核心原因是Oracle PL/SQL的语法限制：PL/SQL流程控制语句（IF/CASE/WHEN等）的判断表达式中，IN列表内不允许直接使用多行子查询，你这里还用到了UNION合并两个子查询结果，完全不符合该位置的语法要求。
另外你的代码还存在一处隐性编译错误：调用F_GET_QAUSR时传入的NEW.CASE_INTRL_ID漏了冒号前缀，触发器内引用当前插入行的字段必须写为:NEW.字段名格式。

最稳妥的改法是把IF条件里的子查询逻辑提前，用EXISTS判断匹配结果存入计数变量，再基于变量值做分支判断，既绕开PL/SQL的语法限制，性能也更好。修改后的完整可编译代码如下：

create or replace TRIGGER TRG_CHK_HRCQA_CASE_ACTIONS
AFTER INSERT ON CUSTOMERS
FOR EACH ROW
DECLARE

 user_audit VARCHAR(30); /* 审计模块展示的用户 */
 user_evdnse VARCHAR(30); /* 证据页签记录操作人 */
 LV_ERRORCODE VARCHAR2(1000);
 ln_match_cnt NUMBER; -- 存储用户匹配结果计数
 
BEGIN
    
  /* 给变量赋值 */
    SELECT OWNER_ID into user_audit from kdd_review_owner  where OWNER_SEQ_ID = :NEW.ACTION_BY_ID;
    SELECT OWNER_ID into user_evdnse from kdd_review_owner  where OWNER_SEQ_ID = :NEW.ACTION_BY_ID;

  /* 判断当前操作人是否属于QA/HRCO用户集合 */
    SELECT COUNT(1) INTO ln_match_cnt
    FROM DUAL
    WHERE EXISTS (
        SELECT 1 FROM table(f_get_arg_table(F_GET_QAUSR(:NEW.CASE_INTRL_ID,user_audit,'AUDIT'))) t
        WHERE t.ENTITY_ID = :NEW.ACTION_BY_ID
        UNION
        SELECT 1 FROM table(f_get_arg_table(F_GET_QAUSR(:NEW.CASE_INTRL_ID,user_evdnse,'EVDNSE'))) t
        WHERE t.ENTITY_ID = :NEW.ACTION_BY_ID
    );
    
    IF ln_match_cnt > 0 THEN
  /* 匹配到QA/HRCO用户则插入IS_HRCO_QA标识为Y的记录 */
        Insert into SC_HRCQA_CASE_ACTIONS (ACTION_SEQ_ID,ACTION_BY_ID,ACTION_TS,STATUS_CD,CASE_INTRL_ID,ACTION_ID,NEW_CASE_OWNR_ASSGN_ID,CASE_DUE_TS,PREV_CASE_OWNR_ASSGN_ID,IS_HRCO_QA) 
        values (:NEW.ACTION_SEQ_ID, :NEW.ACTION_BY_ID, :NEW.ACTION_TS, :NEW.STATUS_CD, :NEW.CASE_INTRL_ID, :NEW.ACTION_ID, :NEW.NEW_CASE_OWNR_ASSGN_ID, :NEW.CASE_DUE_TS, :NEW.PREV_CASE_OWNR_ASSGN_ID,'Y');
    ELSE
  /* 未匹配到则插入IS_HRCO_QA标识为N的记录 */   
        Insert into SC_HRCQA_CASE_ACTIONS (ACTION_SEQ_ID,ACTION_BY_ID,ACTION_TS,STATUS_CD,CASE_INTRL_ID,ACTION_ID,NEW_CASE_OWNR_ASSGN_ID,CASE_DUE_TS,PREV_CASE_OWNR_ASSGN_ID,IS_HRCO_QA) 
        values (:NEW.ACTION_SEQ_ID, :NEW.ACTION_BY_ID, :NEW.ACTION_TS, :NEW.STATUS_CD, :NEW.CASE_INTRL_ID, :NEW.ACTION_ID, :NEW.NEW_CASE_OWNR_ASSGN_ID, :NEW.CASE_DUE_TS, :NEW.PREV_CASE_OWNR_ASSGN_ID,'N');
    END IF;

EXCEPTION
    WHEN OTHERS THEN 
        LV_ERRORCODE := SQLCODE;
        INSERT INTO KDD_LOGS_MSGS (LOG_DT, LOG_INFO_TX, REMARK_TX)
        VALUES (SYSDATE,'ErrorCode - ' || LV_ERRORCODE,'TRG_CHK_HRCQA_CASE_ACTIONS');
END;
/

• 目前你给user_audit和user_evdnse赋值的查询条件完全一致，可以合并成一次查询减少表扫描：

  SELECT OWNER_ID, OWNER_ID INTO user_audit, user_evdnse 
  FROM kdd_review_owner 
  WHERE OWNER_SEQ_ID = :NEW.ACTION_BY_ID;
  

• 异常捕获部分建议同时记录SQLERRM错误信息，只存SQLCODE排查问题时信息不足。

内容的提问来源于stack exchange，提问作者Reeya Oberoi