Error 1054 with trigger after update on table in mysql

Question

I have 3 tables:

master_parts_list

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)

sales_orders

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...

sales_order_parts

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)

What I wanted to do was update the fields quantity_on_hand quantity_allocated quantity_on_order

What I want the outcome to be is: When a record is added to sales_order_parts table, (creating a new item for a sales order) add the order_quantity to quantity_allocated field in master_part_list. When a record is changed or deleted for sales_order_parts for the order_quantity, update the quantity_allocated field. When a record is invoices, as indicated by a value in the invoice_number field, deduct the ship_quantity from the quantity_on_hand. Basic inventory management.

I was wrote a trigger for Insert on sales_order_parts create trigger allocated_addition_insert

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)

That works, after every new record, the code runs and updates quantity_allocated for null invoice_number(meaning not invoiced yet) with the total grouped by part.

I am working on a UPDATE trigger for sales_orders

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$$

The second update runs when invoice_number is null, but when trying to update and save an invoice, which sets an invoice number in invoice_number field, it doesn't work where invoice_number is not null. Basically, when an order gets invoiced, deduct the amount of ship_quantity from the quantity_on_hand in master_part_list, but just the parts for that particular sales order. I get the error:

#1054 - 'Unknown column 'new.sales_orders.invoice_number' in 'where clause"

Any ideas?

The trigger is defined on the sales_orders table. Don't use sales_orders.col_name to access columns, instead use NEW.col_name and OLD.col_name to access the existing value in the column and the new value it is being updated to respectively. — marekful
The UPDATE statements are going to attempt to update every row in master_part_list table. The GROUP BY partid in the subquery is going to potentially cause multiple rows to be returned, thats going to throw a too many rows error in the scalar context (subtracting from quantity on hand.) There's way more wrong here than a syntax error. — spencer7593
Thanks for the response. When I put in: where sales_orders.invoice_number = new.invoice_number and sales_orders.invoice_number is not null, I do not get an error, but it also doesn't update the quantity_on_hand field. No field get updates. It works well for the second update, but I want the first update to just include the new invoice and deduct that from quantity_on_hand. I have sales_orders.invoice_number = new.invoice_number, but it's not returning resulsts. — Paul Santoro
Consider, for a moment, a sequence of updates to a row in sales_orders table, e.g UPDATE sales_orders SET status = 'pending' ..., UPDATE sales_orders SET customer_po = '1234' ... , UPDATE sales_orders SET status = 'fulfillment' ... the trigger is going to get fired each time, and we are going to update quantity_on_hand and quantity_on_order... Q: Do we really want to subtract from quantity_on_hand / add to quantity_on_order every time an update is applied to a row in sales_order ? — spencer7593
If I start from scratch with the triggers, using the current tables and field names, what would be a good method to calculate and keep up to date, quantity_on_hand, quantity_allocated, quantity_on_order (I'm going to create a Purchase table, assume it will have part id, quantity_ordered, quantity_received, date_received fields? — Paul Santoro

Paul Santoro Paul Santoro · Accepted Answer · 2019-01-17T16:34:07

After some work, I figured it out.
Here is the code for the trigger for insert on sales_order_parts. It sums up the value of all open orders order_quantity field grouped by part and sets the value in quantity_allocated field in master_parts_list. It leaves out all quotes and already invoiced orders.

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);

This is to update the value on a changed quantity in sales_order_parts Here is the code for the trigger for before update on sales_order_parts.

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;

Here is the code for the trigger for after update on sales_order_parts.

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;

Here is the code for the update on sales_orders.

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 ;

These's trigger scripts works well for me, but any suggestions or comments are welcome. Thanks.

Error 1054 with trigger after update on table in mysql

1 Answers