【MySQL】物化视图（2）

CREATE TRIGGER purchase_mv_trigger_ins AFTER INSERT ON purchase_order FOR EACH ROW
BEGIN
    SET @old_pro_price_sum = 0;
    SET @old_pro_price_avg = 0;
    SET @old_pro_num_sum = 0;
    SET @old_pro_num_avg = 0;
    SET @old_pro_count = 0;
    # 查询出之前的信息然后记录到不同的变量中
    SELECT
        IFNULL(pro_price_sum,0),IFNULL(pro_price_avg,0),
        IFNULL(pro_num_sum,0),IFNULL(pro_num_avg,0),
        IFNULL(pro_count,0)
    FROM
        purchase_mv
    WHERE
        supply_name = NEW.supply_name
    INTO
        @old_pro_price_sum,@old_pro_price_avg,@old_pro_num_sum,@old_pro_num_avg,@old_pro_count;
    # 然后再去计算更新操作之后的内容
    SET @new_pro_count = @old_pro_count + 1;
    SET @new_pro_price_sum = @old_pro_price_sum + NEW.pro_price;
    SET @new_pro_price_avg = @new_pro_price_sum / @new_pro_count;

    SET @new_pro_num_sum = @old_pro_num_sum + NEW.pro_num;
    SET @new_pro_num_avg = @new_pro_num_sum / @new_pro_count;

    REPLACE INTO
        purchase_mv
    VALUES(
        NEW.supply_name, @new_pro_count,
        @new_pro_price_sum, IFNULL(@new_pro_price_avg, 0),
        @new_pro_num_sum, IFNULL(@new_pro_num_avg, 0)
    );
END;