【MySQL】在一个trigger下实现insert or update 执行某语句块时失败

Question

需求：创建触发器tri_OrderDetailInsertUpdate，当在 orderdetail表中插入或修改订单时,计算 orders表中相关订单的TotalPrice 。

CREATE TRIGGER tri_OrderDetailInsertUpdate
AFTER UPDATE OR INSERT ON orderdetail FOR EACH ROW
BEGIN
UPDATE orders
SET TotalPrice=(SELECT SUM(Amount*Price*Discount) FROM book JOIN orderdetail ON book.BookCode=orderdetail.BookCode GROUP BY orderdetail.OrderCode)
WHERE OrderCode=new.OrderCode
END;

报错：1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OR INSERT ON orderdetail FOR EACH ROW
BEGIN
UPDATE orders
SET TotalPrice=(SEL' at line 2

在https://blog.csdn.net/home_zhang/article/details/86590527 这篇文章中写的也是insert or update

因此到底有没有 insert or update 这种写法呢？我用着是报错，下载的Navicat也是最新版的

Answer 1

mysql不像oracle那样可以支持定义多个触发事件,不同触发事件就得建不同的触发器,下面是mysql触发器的创建语法

CREATE
    [DEFINER = user]
    TRIGGER [IF NOT EXISTS] trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

官方文档

https://dev.mysql.com/doc/refman/8.0/en/create-trigger.html

官方用例

MySQL :: MySQL 8.0 Reference Manual :: 25.3.1 Trigger Syntax and Examples https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html