How to create trigger to update columns that is calculated from another updated table

Question

I have three tables:

Product(pid, pname, pbrand, pprice)

ShoppingCart(cartid, cid, active, totalprice) ShoppingCart.cid references to Customer.cid

CartItem(cartid, pid, iprice, iquantity) CartItem.cartid references ShoppingCart.cartid, CartId.pid references Product.pid

if the product price in Table Product is updated, it should first update all carts(CartItem) that contain that product, then update the totalprice calculated in the related shoppingcart.

I can finish the first part and I expect the second task could work like

delimiter //

create trigger update_prodprice 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=True);

  update shoppingcart set totalprice=sum(cartitem.iprice*cartitem.iquantity)
  where active=True and cartid=cartitem.cartid;

  end //

delimiter ;

But the second update does not work because of

"ERROR 1054 (42S22): Unknown column 'cartitem.cartid' in 'where clause'"

Tin Tran · Answer 1 · 2016-04-07T23:37:11.730

you can update multiple tables like this

update shoppingcart s, cartitem c 
  set c.iprice = new.pprice,
      s.totalprice = c.iprice*c.iquantity
  where s.active=True and s.cartid=c.cartid
  and c.pid = new.pid;

but you have to work out how you update total price since a cart can have multiple items maybe something like this

update shoppingcart s, cartitem c 
  set c.iprice = new.pprice,
      s.totalprice = s.totalprice - old.pprice*c.iquantity + new.pprice*c.iquantity
  where s.active=True and s.cartid=c.cartid
  and c.pid = new.pid;

sqlfiddle

How to create trigger to update columns that is calculated from another updated table

1 Answers1