MySQL创建TRIGGER触发器报1442错误及语法错误解决方法

问题1：updateWidgetSale触发器创建语法报错 #

报错原因 #

SIGNAL语句语法写法错误，冗余了VALUE关键字，且触发器定义结尾的END后缺少语句结束标识。MySQL没有Oracle、PostgreSQL中的RAISE函数，自定义异常抛出直接使用SIGNAL SQLSTATE即可，不需要额外加VALUE关键字。

修复后可正常运行的触发器代码 #

delimiter //
CREATE TRIGGER updateWidgetSale BEFORE UPDATE ON widgetSale FOR EACH ROW
BEGIN
IF NEW.reconciled = 1 THEN
SIGNAL SQLSTATE '45000'
  SET MESSAGE_TEXT = 'cannot update table "widgetSale" after it has been reconciled';
END IF;
END //
delimiter ;

创建完成后，尝试更新reconciled=1的行就会抛出自定义异常，拦截非法更新操作。

问题2：stampSale触发器运行触发1442错误 #

报错原因 #

错误码1442是MySQL的触发器递归保护机制触发，核心原因有两点：

1. 最初版本的触发器使用了AFTER INSERT触发时机：AFTER时机下目标行数据已经写入widgetSale表，此时既不能修改NEW.开头的待插入行字段，若代码中误写对widgetSale表本身的更新操作，会再次触发同表触发器进入无限递归，MySQL会直接抛出1442错误终止执行。
2. 你当前贴出的BEFORE INSERT版本代码中，更新widgetCustomer表stamp字段的语句缺少WHERE过滤条件，会全表更新所有客户的stamp值，不符合业务预期。

修复后可正常运行的触发器代码 #

delimiter //
CREATE TRIGGER stampSale BEFORE INSERT ON widgetSale FOR EACH ROW 
BEGIN
-- BEFORE时机下可以直接修改NEW字段值，会随插入语句一同写入表
SET NEW.stamp = CURDATE();
-- 更新关联客户表的最近订单ID，加WHERE条件精准匹配当前下单客户
UPDATE widgetCustomer SET last_order_id = NEW.item_id WHERE id = NEW.customer_id;
-- 更新同客户的时间戳，必须加WHERE条件，避免全表更新
UPDATE widgetCustomer SET stamp = NEW.stamp WHERE id = NEW.customer_id;
-- 写入操作日志
INSERT INTO widgetLog (stamp, event, username, tablename, table_id) 
VALUES (NEW.stamp, 'INSERT ', 'TRIGGER', 'widgetSale', NEW.customer_id);
END //
delimiter ;

验证逻辑 #

触发器创建完成后执行预设的3条widgetSale插入语句，再查询三张表即可得到预期结果：

• widgetSale的每条记录都会自动带上插入当天的stamp值
• widgetCustomer中对应ID的客户会更新last_order_id和stamp字段
• widgetLog中会生成3条对应的插入日志，不会触发1442错误

注意：所有触发器创建完成后，记得把分隔符改回默认的分号;，避免后续执行SQL语句出现分隔符不匹配的语法错误。

内容的提问来源于stack exchange，提问作者Anna Quoc Nguyen