mysql - 如何编写一个接一个地更新两个表的触发器

Question

Customer(cid, cname, caddress, ccity, cstate, czip, ccardnum)Product(pid, pname, pbrand, pprice)ShoppingCart(cartid, cid, active, totalprice)          ShoppingCart.cid references to Customer.cidCartItem(cartid, pid, iprice, iquantity)     CartItem.cartid references ShoppingCart.cartid, CartId.pid references Product.pidOrder(oid, cartid, time, payprice) Order.cartid references to ShoppingCart.cartid

Create triggers that, when the price of a certain product changes, update the price of this item in any active shopping cart containing the item, and also updates the total price of these shopping carts?.

CREATE TRIGGER priceupdate AFTER UPDATE ON product
FOR EACH ROW
BEGIN
UPDATE cartitem 
SET iprice=new.pprice 
WHERE pid=new.pid 
and cartid in 
(select cartid                                           
from shoppingcart WHERE active='1');

UPDATE shoppingcart 
SET totalprice = 
(select sum(iprice*iquantity) 
from cartitem WHERE cartid=new.cartid;
END;

上面的代码给我错误，因为我正在更新用于更新 cartitem 的购物车
当我尝试下面给出的代码时工作正常但我无法更新购物车中的总价。有人可以帮我更新购物车总价

CREATE TRIGGER priceupdate AFTER UPDATE ON product
FOR EACH ROW
BEGIN
UPDATE cartitem 
SET iprice=new.pprice 
WHERE pid=new.pid and cartid in 
(select cartid                                           
from shoppingcart WHERE active='1');
END;

注意:我使用的是MYSQL

示例数据:http://www.sqlfiddle.com/#!2/8489e9/4/3

Answer 1

尝试

DELIMITER $$
CREATE TRIGGER product_priceupdate 
AFTER UPDATE ON product
FOR EACH ROW
BEGIN
  UPDATE cartitem i JOIN shoppingcart c
      ON i.cartid = c.cartid
     SET i.iprice = NEW.pprice 
   WHERE i.pid = NEW.pid 
     AND c.active  = 1;

  UPDATE shoppingcart c JOIN
 (
   SELECT cartid, SUM(iprice * iquantity) totalprice
     FROM cartitem 
    WHERE cartid IN
          (
            SELECT DISTINCT i.cartid 
              FROM cartitem i JOIN shoppingcart c
                ON i.cartid = c.cartid
             WHERE i.pid = NEW.pid 
               AND c.active  = 1
          )
    GROUP BY cartid
 ) q
     ON c.cartid = q.cartid
    SET c.totalprice = q.totalprice;
END$$
DELIMITER ;

这是 SQLFiddle 演示