Python社区  »  DATABASE

MySQL表更新后触发器出现错误1054

Paul Santoro • 1 周前 • 22 次点击  

我有三张桌子:

主部件清单

id int(11) not null auto increment
part_number varchar(30) unique
description varchar(255)
price decimal(10,2)
weight decimal(10,2)
active tinyint(1)
quantity_on_hand decimal(10,2)
quantity_allocated decimal(10,2)
quantity_on_order decimal(10,2)
old_part_number varchar(30)

销售订单

id int(11) not null primary key auto increment
quote_number int(11) unique
order_number int(11) unique
invoice_number int(11) unique
status varchar(255)
customer_number int(11)
customer_po_number char(30)
...and 15 others...

销售订单部件

id int(11) not null primary key auto increment
order_id int(11)
part_id int(5)
order_quantity int(5)
ship_quantity int(5)
unit_price decimal(10,2)
note varchar(255)

我想做的是更新字段 现存量 分配数量 订购数量

我希望结果是: 将记录添加到Sales_Order_Parts表时,(为Sales Order创建新项目)将Order_Quantity添加到Master_Part_List中的Quantity_Allocated字段。当更改或删除订单数量的销售订单部件记录时,更新“数量分配”字段。当记录为发票时,如“发票号”字段中的值所示,从现存量中扣除装运数量。基本库存管理。

我在销售订单上写了一个插入触发器 创建触发器分配的附加插入

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
group by 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-(select sum(ship_quantity)
    from
    sales_orders
        join
    sales_order_parts on sales_orders.id = sales_order_parts.order_id
    where sales_orders.invoice_number = new.sales_orders.invoice_number
    and sales_orders.invoice_number is not null
    group by part_id);

    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
    group by part_id);
end$$

第二次更新在Invoice_Number为空时运行,但当尝试更新和保存Invoice(在Invoice_Number字段中设置发票编号)时,在Invoice_Number不为空时不起作用。基本上,当一个订单收到发票时,从主部件清单上的数量中扣除发货数量的金额,但只扣除特定销售订单的部件。我知道错误:

#1054-“where子句”中的“未知列”new.sales_orders.invoice_number”

有什么想法吗?

Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/46579
 
22 次点击  
分享到微博
文章 [ 1 ]  |  最新文章 1 周前
Paul Santoro
Reply   •   1 楼
Paul Santoro    9 月前

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

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 ;

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