mysql 触发器更新自己表|单表操作

最新推荐文章于 2025-09-02 13:50:44 发布

原创最新推荐文章于 2025-09-02 13:50:44 发布 · 6.7k 阅读

2 ·

CC 4.0 BY-SA版权

MongoDB 专栏收录该内容

12 篇文章

订阅专栏

本文探讨了在数据库中使用SQL触发器更新表`m_order`的`status_id`的最佳实践，解决了常见错误并提供了解决方案。

#更新表`m_order`的status_id，
#注意要用BEFORE
#SQL语句不能用UPDATE，要用SET
CREATE TRIGGER `tg_order2order_update` BEFORE UPDATE ON `m_order` FOR EACH ROW BEGIN 
	set new.`status_id` = (SELECT `status_id`  FROM m_status WHERE `id` =  new.`order_id`) ;
END;

常会报错如：

updating of new row is not allowed in after trigger

Can’t update table ‘dept’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.