MYSQL 触发错误如何解决？

Question

MYSQL:

DELIMITER $$create TRIGGER `unit` beforeinsert ON `cart` FOR each row 
BEGIN
IF(EXISTS 
  ( 
         SELECT 1 
         FROM   `cart` 
         WHERE  `USER` = new.USER 
         AND    `item` = new.item)) THEN 
  UPDATE `cart` 
  SET    `unit` = new.unit, 
         `quantity` = new.quantity 
  WHERE  `USER` = new.USER 
  AND    `item` = new.item;ENDIF;
END 
$$ delimiter ;

错误:

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

当我尝试插入数据(如果已经存在)时，它会显示错误。如果该商品已在购物车中，我想更新数据。我该如何解决这个错误？

Answer 1

您不能在 INSERT 触发器内部进行 UPDATE 操作来代替执行 INSERT。您要么需要在代码中插入之前检查记录，要么考虑使用 ON DUPLICATE KEY UPDATE 语句:

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;