如何创建Teradata插入/更新后触发器 解决空值替换触发报错

报错根因 #

你收到的5423错误是因为INSERT类型的触发器不存在OLD数据集，你在REFERENCING子句中额外声明了OLD table as old_clients_table，属于非法语法，因此触发了校验失败。

原写法的其他问题 #

• 你使用了AFTER语句级触发器，内部执行的全表UPDATE会把所有历史未设置NEEDS_AUTHENTICATION的行全部修改为N，不符合仅处理新插入/更新行的需求
• 你需要同时覆盖插入、更新两种场景的校验，原触发器只绑定了INSERT事件，无法满足更新校验需求

最优解决方案 #

建议使用行级BEFORE触发器，直接修改新行的字段值，不需要事后更新表数据，性能更高且不会误改历史数据，语法如下：

CREATE OR REPLACE TRIGGER DB.C_UP_CLIENTS
BEFORE INSERT OR UPDATE ON DB.CLIENTS
FOR EACH ROW
BEGIN
  -- 仅当新行该字段为空时赋值为N
  IF NEW.NEEDS_AUTHENTICATION IS NULL THEN
    SET NEW.NEEDS_AUTHENTICATION = 'N';
  END IF;
END;

如果你的数据库不支持CREATE OR REPLACE语法，可以先删除旧触发器再创建：

DROP TRIGGER IF EXISTS DB.C_UP_CLIENTS;
CREATE TRIGGER DB.C_UP_CLIENTS
BEFORE INSERT OR UPDATE ON DB.CLIENTS
FOR EACH ROW
BEGIN
  IF NEW.NEEDS_AUTHENTICATION IS NULL THEN
    SET NEW.NEEDS_AUTHENTICATION = 'N';
  END IF;
END;

兼容AFTER语句级触发器的写法 #

如果你受数据库限制必须使用AFTER语句级触发器，需要拆分插入、更新两个触发器，且删除OLD表的声明，同时限定仅修改新操作的行（需要表有唯一主键）：

插入触发器 #

CREATE OR REPLACE TRIGGER DB.C_INS_CLIENTS
AFTER INSERT ON DB.CLIENTS
REFERENCING NEW TABLE AS new_clients_table
FOR EACH STATEMENT
UPDATE DB.CLIENTS 
SET NEEDS_AUTHENTICATION = 'N' 
WHERE 
  NEEDS_AUTHENTICATION IS NULL 
  AND 你的表主键列 IN (SELECT 你的表主键列 FROM new_clients_table);

更新触发器 #

CREATE OR REPLACE TRIGGER DB.C_UPD_CLIENTS
AFTER UPDATE ON DB.CLIENTS
REFERENCING NEW TABLE AS new_clients_table
FOR EACH STATEMENT
UPDATE DB.CLIENTS 
SET NEEDS_AUTHENTICATION = 'N' 
WHERE 
  NEEDS_AUTHENTICATION IS NULL 
  AND 你的表主键列 IN (SELECT 你的表主键列 FROM new_clients_table);

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