price_avg = price_sum /IF(orders_cnt>0, orders_cnt, 1)

where product_name = OLD.product_name;

set @old_price_sum = 0;

set @old_amount_sum = 0;

set @old_price_avg = 0;

set @old_orders_cnt = 0;

select IFNULL(price_sum, 0), IFNULL(amount_sum, 0), IFNULL(price_avg, 0), IFNULL(orders_cnt, 0) www.2cto.com

from orders_mv

where product_name = NEW.product_name

into @old_price_sum, @old_amount_sum, @old_price_avg, @old_orders_cnt;

set @new_price_sum = @old_price_sum + NEW.price;

set @new_amount_sum = @old_amount_sum + NEW.amount;

set @new_orders_cnt = @old_orders_cnt + 1;

set @new_price_avg = @new_price_sum / @new_orders_cnt;

replace into orders_mv

values (NEW.product_name, @new_price_sum, @new_amount_sum, @new_price_avg, @new_orders_cnt);

else

update orders_mv

set

price_sum = (price_sum - OLD.price + NEW.price),

amount_sum = (amount_sum - OLD.amount + NEW.amount),

//错误，此时的price_sum已经是新值, 不能重新 -OLD.price + NEW.price

//price_avg = (price_sum - OLD.price + NEW.price) /IF(orders_cnt>0,orders_cnt,1)

price_avg = price_sum /IF(orders_cnt>0,orders_cnt,1)

where product_name = OLD.product_name;

end if;

end;

$$

delimiter ;

5，为表orders创建after delete的触发器

drop trigger tgr_orders_delete;

delimiter $$

create trigger tgr_orders_delete

after delete on orders

for each row

begin www.2cto.com

update orders_mv

set

price_sum = (price_sum - OLD.price),

amount_sum = (amount_sum - OLD.amount),

orders_cnt = (orders_cnt - 1),

price_avg = price_sum /IF(orders_cnt>0, orders_cnt, 1)

where product_name = OLD.product_name;

end;

$$

delimiter ;

作者 tenfyguo