CODEWITHSUNDEEP
mysqldatabase
user3365101
user3365101

Reputation: 51

Trigger to decrease a counter

I want to create a trigger to decrease the quantity of available items after an order has been made.

I tried to create a trigger. I'm using phpMyAdmin, setting trigger AFTER INSERT in the table products_in_order

       SET @qtt = (
           SELECT quantity  
           FROM products_in_order
           ORDER BY inorder_id DESC
           LIMIT 1)

       SET @code = (
           SELECT product_code  
           FROM products_in_order
           ORDER BY inorder_id DESC
           LIMIT 1)

           UPDATE products 
           SET quantity = quantity-@qtt
           WHERE product_code=@code

Why does the first SET work, and the second, too, but as soon as I write them both, i get an error?

How to make this trigger work correctly?

Sample data:

products represents all products available in the shop, and has columns:

product_code | name | price | quantity

product_in_order represents a set of products of the same type, that can be added to order. It has colums:

inorder_id | product_code | price | order_no 

inorder_id | product_code | price | quantity | order_no

When somebody adds a set of products to the order, I want the overall quantity of that product to decrease.

Upvotes: 1

Views: 801

Answers (1)

StuartLC
StuartLC

Reputation: 107317

You don't need to join back to the newly inserted / updated table* - just use the New pseudo row from within your trigger, which already has the values you need to join into the master Product table:

CREATE TRIGGER `MyTrigger` 
    AFTER INSERT ON `products_in_order`
    FOR EACH ROW 
    BEGIN
      UPDATE products
           SET quantity = quantity-New.quantity
           WHERE product_code=New.product_code;
    END;

SqlFiddle here demonstrating that the starting value of Product is depleted by the amount inserted into products_in_order for the applicable product code.

* Assuming inorder_id is an AUTO_INCREMENT, selecting from this table twice without any locking considerations will also be prone to concurrency issues, e.g. if another writer inserts another row while your trigger is executing. Another good reason to use New

Upvotes: 1

Related Questions