I am new to MySQL and learning about trigger. I have 2 tables that I want : when a table (detail_transaction) has been inserted, a ‘stock’ field of another table (item) change.
- ‘item’ Table
id | name | price | stock |
---|---|---|---|
1 | Item_A | 15 | 900 |
2 | Item_B | 9 | 500 |
- ‘detail_transaction’ Table
id | id_item | count | total_price |
---|---|---|---|
1 | 1 | 5 | 75 |
If I insert new row in ‘detail_transaction’ table, I WANT my ‘stock’ field in ‘item’ table with the same ‘id’ to decrease and adjust to the ‘count’ of the ‘detail_transaction’. For example :
I insert new row in ‘detail_transaction’ table :
id | id_item | count | total_price |
---|---|---|---|
2 | 1 | 10 | 150 |
I WANT the ‘item’ table updated to :
id | name | price | stock |
---|---|---|---|
1 | Item_A | 15 | 890 |
2 | Item_B | 9 | 500 |
I created a trigger to try achieve my purpose, but when I tried to insert new row in ‘detail_transaction’ I got this error : Can’t update ‘item’ table in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
My trigger :
DELIMITER $$
CREATE TRIGGER update_stock
AFTER INSERT
ON detail_transaction
FOR EACH ROW
BEGIN
UPDATE item
JOIN detail_transaction ON detail_transaction.id_item = item.id
SET stock = stock - NEW.count
WHERE item.id = NEW.id_item;
END$$
DELIMITER ;
Then, I inserted row to detail_transaction table :
INSERT INTO detail_transaction (id, id_item, count, total_price)
VALUES (2, 1, 10, (SELECT price FROM item WHERE item.ID = 1) * 10);
But I got the error. What can I do to solve this? Is it because of the SELECT part when I try to INSERT? Thanks for your answer.
3
Answers
Firstly (and opinionated): triggers are hard to debug, test and maintain. Systems that include triggers are really hard to debug because they introduce side effects – "I did X on this table, and then Y happened on a different table". As a developer, you have to keep all the triggers in your head to understand what an individual statement might do.
If we take your example, for instance, you might have a trigger on the "stock" field in
Item
to create a purchase order record to replenish the stock if it falls below a threshold. Thepurchase order
table might have an insert trigger to create a record inaccounts payable
, which might have an insert trigger to reject records if the total balance for a given vendor exceeds a threshold. That chain of triggers implements valid business logic, but results in really complex debugging process when suddenly an insert intodetail_transaction
is rejected because the product vendor exceeds their payment limit. (And yes, I have seen this kind of scenario!).One of the challenges with triggers is that the database engine does not want an infinite loop to happen, or to have the value of the field you are SELECTing changing as a result of the trigger firing.
Also, you don’t need that join – you can get the values from NEW.
The way to do this is to use a variable:
See fiddle.
EDIT – some of the other answers show a simpler solution – calculating the total price in a trigger.
Reasonable people disagree about how to use triggers – but I would suggest that using triggers to calculate derived values – "total stock for a given item", or "total price of a transaction" – is often a bad idea. You’re effectively duplicating data – the total stock level for an item is both the sum of transactions, and the attribute in a row. The total price is both "price * quantity", and an attribute in a row. What happens if someone executes an update statement for total_price or total_stock (either intentionally or as part of a bug)? Which value is correct?
You should not mix insert..values and insert..select I would rewrite the insert as
Although my choice would be a before insert trigger
with an insert
The after insert publish by you fails because you use a multi table update invoking a table which fired the trigger, this is not allowed , the resolution of this issue appear in a previous answer.
fiddle
PS. Each trigger contains only one statement. So neither BEGIN-END noк DELIMITER command needed.