0

So I have these three tables. The orders table is list of orders existed, while the order details consist of items that is in an order (because 1 order can have more than one catalog). I'm using mysql

Cust_order

Order_id Name Email Shipping_Status
1 Bryan [email protected] Shiped

Order_item

Od_id Order_id Catalog_id Quantity
1. 1 1 5
2. 1 2 5

Inventory

Catalog_id quantityInStock
1 10
2 10

I want to create a trigger that if the shipping_status in orders table is 'shipped', then it will update the stock in inventory.

I've created this trigger

create trigger update_inventory
after update on cust_order
for each row 
begin
if new.shipping_status="Shipped" then
update inventory 
set inStock = inStock - (select quantity from order_item where order_id=new.order_id)
where catalog_id in (select catalog_id from order_item where order_id=new.order_id);
end if;
end;
//

But it doesn't work and turns #1242 - Subquery returns more than 1 row

How to create trigger that updates multiple row at once?

0

2 Answers 2

0
CREATE TRIGGER update_inventory
AFTER UPDATE ON cust_order
FOR EACH ROW 
UPDATE inventory 
JOIN order_item USING (catalog_id)
SET inventory.quantityInStock = inventory.quantityInStock - order_item.quantity
WHERE cust_order.order_id = NEW.order_id
  AND NEW.shipping_status = 'Shipped';

The query needs order_item (order_id, catalog_id) to be defined as UNIQUE in the table structure. If not then you must use aggregating subquery instead of single table in JOIN order_item.

0

Triggers are rather limited in what they can do.

Instead, write a Stored Procedure to encapsulate the set of operations that you need to perform "atomically". And be sure to put them in a "transaction".

1
  • I do not see the limitation which prevents the trigger usage - except incorrect logic during its creation.
    – Akina
    Oct 7, 2022 at 4:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.