Paul Santoro

经过一番努力,我终于明白了。
这是用于插入销售订单部件的触发器的代码。 它汇总按部件分组的所有未结订单订单数量字段的值,并在主部件列表的数量分配字段中设置值。它漏掉了所有报价和已经开发票的订单。

create trigger allocated_addition_insert
after insert on sales_order_parts
for each row
update master_part_list
set master_part_list.quantity_allocated=
    (select sum(order_quantity)
from
    sales_orders
        inner join
    sales_order_parts on sales_orders.id = sales_order_parts.order_id
where sales_orders.invoice_number is null 
and sales_order_parts.part_id = master_part_list.id 
and sales_orders.status <> "Quote" 
group by part_id);

这是更新销售订单部件中更改数量的值 以下是销售订单部件更新前的触发器代码。

create trigger allocated_minus_update
before update on sales_order_parts
for each row
    update master_part_list set master_part_list.quantity_allocated = master_part_list.quantity_allocated - old.order_quantity
    where master_part_list.id = old.part_id;

这是销售订单部件更新后触发器的代码。

create trigger allocated_add_update
after update on sales_order_parts
for each row
    update master_part_list set master_part_list.quantity_allocated = master_part_list.quantity_allocated + new.order_quantity
    where master_part_list.id = new.part_id;

这是销售订单更新的代码。

delimiter $$
create trigger allocated_order_on_hand_update
after update on sales_orders
for each row begin
    update master_part_list
    set master_part_list.quantity_on_hand = master_part_list.quantity_on_hand -
    coalesce((select sum(ship_quantity)
    from
    sales_order_parts
            inner join
    sales_orders on sales_orders.id = sales_order_parts.order_id
    where sales_orders.invoice_number = new.invoice_number
    and sales_order_parts.part_id = master_part_list.id
    and new.invoice_number > 0
    group by sales_order_parts.part_id),0);

    update master_part_list
    set master_part_list.quantity_allocated=
    (select sum(order_quantity)
    from
    sales_orders
        inner join
    sales_order_parts on sales_orders.id = sales_order_parts.order_id
    where sales_orders.invoice_number is null 
    and sales_order_parts.part_id = master_part_list.id 
    and sales_orders.status <> "Quote" 
    group by part_id);
end$$
delimiter ;

这些触发脚本对我很好,但欢迎任何建议或评论。谢谢。