MySQL触发器开发求助：自动计算purchase_data表total_price字段

错误原因 #

报错Can't update table 'purchase_data' in stored function/trigger because it is already used by statement which invoked this stored function/trigger的根源是使用了AFTER INSERT触发器：此时触发插入的SQL语句尚未完全执行完毕，purchase_data表处于被占用状态，MySQL不允许在这种情况下对该表执行额外的UPDATE操作。

解决方案 #

改用BEFORE INSERT和BEFORE UPDATE触发器，在数据写入/更新到表之前，直接计算并赋值给NEW.total_price，无需执行UPDATE语句，从根本上避免同表操作冲突。

1. 插入时自动计算的触发器

CREATE DEFINER=`root`@`localhost` TRIGGER `calculate_total_price_insert` BEFORE INSERT ON `purchase_data` FOR EACH ROW
BEGIN
    -- 从product表获取单价，计算后赋值给新插入记录的total_price
    SELECT p.product_price * NEW.product_count INTO NEW.total_price
    FROM product p
    WHERE p.product_id = NEW.product_id;
END

2. 更新时自动计算的触发器

当purchase_data表的product_count或product_id字段被更新时，需要重新计算total_price，因此需要创建BEFORE UPDATE触发器：

CREATE DEFINER=`root`@`localhost` TRIGGER `calculate_total_price_update` BEFORE UPDATE ON `purchase_data` FOR EACH ROW
BEGIN
    -- 重新计算更新后的total_price
    SELECT p.product_price * NEW.product_count INTO NEW.total_price
    FROM product p
    WHERE p.product_id = NEW.product_id;
END

补充说明 #

• 由于purchase_data表的product_id字段有外键约束关联到product表，因此无需担心查询不到对应product_price的情况。
• 触发器中直接修改NEW对象的字段值，最终写入/更新到表中的数据会自动带上计算好的total_price，无需额外操作。

相关表结构 #

purchase_data表

CREATE TABLE `Purchase_data` (
  `purchasedata_id` INT NOT NULL AUTO_INCREMENT,
  `purchase_id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `product_count` INT NOT NULL,
  `total_price` DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (`purchasedata_id`),
  INDEX `Purchase_idx` (`purchase_id` ASC) VISIBLE,
  INDEX `Product_idx` (`product_id` ASC) VISIBLE,
  CONSTRAINT `Purchase`
    FOREIGN KEY (`purchase_id`)
    REFERENCES `DB_OnlineTrade`.`Purchase` (`purchase_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `Product`
    FOREIGN KEY (`product_id`)
    REFERENCES `DB_OnlineTrade`.`Product` (`product_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)

product表

CREATE TABLE `Product` (
  `product_id` INT NOT NULL AUTO_INCREMENT,
  `product_name` VARCHAR(45) NOT NULL,
  `date_pub` DATETIME NOT NULL,
  `publisher_id` INT NOT NULL,
  `category_id` INT NOT NULL,
  `product_price` DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (`product_id`),
  INDEX `Categoty_idx` (`category_id` ASC) VISIBLE,
  INDEX `Publisher_idx` (`publisher_id` ASC) VISIBLE,
  CONSTRAINT `Publisher`
    FOREIGN KEY (`publisher_id`)
    REFERENCES `DB_OnlineTrade`.`Publisher` (`publisher_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `Category`
    FOREIGN KEY (`category_id`)
    REFERENCES `DB_OnlineTrade`.`Category` (`category_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)

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