0

I am trying to create a Trigger to reduce the quantity of stock from my Products table when an order is received in my Orders table.

So far I have come up with the code on this DB Fiddle, which includes my query attempt. It seems to run without error but it does not reduce the Stocklevel.

I have read the standard documentation on Triggers but can't find a solution to this. Any ideas?

0

Taking your code,

DELIMITER //

CREATE TRIGGER UpdateStock
AFTER INSERT ON ORDERS.Quantity
FOR EACH ROW

BEGIN
   UPDATE PRODUCT.Stocklevel
   SET New.PRODUCT.StockLevel = New.PRODUCT.StockLevel - New.ORDERS.Quantity
   WHERE ORDERS.Quantity = NEW.Quantity;

END;

// DELIMITER;

, I can hardly believe that you've read and understood any documentation -- not on triggers and not on updates at least. And it surely does not compile, let alone run even with errors...

(And by the way, why not posting the relevant code here but instead on an other platform? Makes things unnecessarily harder, I think.)

The ON clause of a trigger takes a table, not a column. So change ON ORDERS.Quantity to ON ORDERS.

After UPDATE follows a table, not a column. So change UPDATE PRODUCT.Stocklevel to UPDATE PRODUCT.

Via the the NEW pseudo table the values of the inserted row can be accessed in a FOR EACH ROW trigger. But not any other table or their columns or the table on which the trigger is executed, for that matter. So New.PRODUCT.StockLevel and New.ORDERS.Quantity are nonsense. Change SET New.PRODUCT.StockLevel = New.PRODUCT.StockLevel - New.ORDERS.Quantity to SET StockLevel = StockLevel - New.Quantity.

And finally, there is no column Quantity in your table ORDERS. So WHERE ORDERS.Quantity = NEW.Quantity; is just wrong either. And even if there were such a column, it would mean the stock level was set at all products having the same quantity (whatever that should mean in products) as the row inserted into order. I guess you want to update the stock level of the product in the order, so use the product id: WHERE ProductID = NEW.ProductID;

That should do the trick.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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