Oracle触发器实现多操作报PLS-00201 NEW.NAME未声明解决

背景 #

需要将CSV格式数据导入名为MYDATA的数据库表，表结构及示例数据如下：

+----+-------+-------+---------+
| ID | NAME  | STATE |  CODE   |
+----+-------+-------+---------+
|  1 | P&P   | AQ    | BIN1234 |
|  2 | ABC   | AQ    | BIN5678 |
|  3 | G-I   | AQ    | BIN3457 |
|  4 | MC-DO | AQ    | BIN3462 |
|  5 | TEC   | AQ    | ERP9756 |
|  6 | CBA   | BT    | ERP4353 |
|  7 | W&X   | BT    | ERP5456 |
|  8 | P-Q   | GH    | ERP3457 |
+----+-------+-------+---------+

数据写入时需要执行以下处理规则：

• 删除STATE列值不等于'AQ'的行
• 删除CODE列值匹配'%ERP%'的行
• 将NAME列中的特殊字符&（和号）、-（连字符）替换为_（下划线）
• 去除NAME列值的首尾空白字符

初始代码报错 #

最初编写的触发器创建失败，代码如下：

CREATE OR replace TRIGGER BI_MYDATA_TR
  BEFORE INSERT OR UPDATE ON MYDATA
BEGIN
    DELETE FROM MYDATA
    WHERE  upper(state) <> 'AQ'
    OR upper(code) LIKE '%ERP%'; 

    :NEW.name := trim(regexp_replace(:NEW.name, '&|-', '_'));
END; 

报错信息：

第3行错误：PL/SQL: 语句被忽略 第3行：PLS-00201: identifier 'NEW.NAME' must be declared（标识符'NEW.NAME'必须声明）

1. create or replace trigger BI_MYDATA_TR
2. before insert or update on MYDATA
3. begin
4. DELETE FROM MYDATA WHERE upper(state) <> 'AQ' OR upper(code) LIKE '%ERP%';
5. :NEW.name := trim(regexp_replace(:NEW.name, '&|-', '_'));

修正方案 #

@Florin 提供的代码可正常实现所有需求，修正后的完整触发器代码如下：

CREATE OR replace TRIGGER BIU_MYDATA_TR
  BEFORE INSERT OR UPDATE ON MYDATA
  REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
    DELETE FROM MYDATA
    WHERE  upper(STATE) <> 'AQ'
    OR upper(CODE) LIKE '%ERP%'; 

    :NEW.NAME := trim(regexp_replace(:NEW.NAME, '&|-', '_'));
END; 

原报错的核心原因是未添加FOR EACH ROW声明，触发器默认是语句级，无法通过:NEW标识符访问单行写入数据。

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