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.

CodePudding user response：

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. The purchase order table might have an insert trigger to create a record in accounts 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 into detail_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.

DELIMITER $$
CREATE TRIGGER update_stock
AFTER INSERT
    ON detail_transaction 
    FOR EACH ROW
BEGIN
    UPDATE item
    SET stock = stock - NEW.count
    WHERE item.id = NEW.id_item;
END$$
DELIMITER ;

The way to do this is to use a variable:

SET @PRICE = ((SELECT price FROM item WHERE item.ID = 1) * 10);


INSERT INTO detail_transaction (id, id_item, count, total_price)
VALUES (2, 1, 10, @PRICE);

SELECT * from item;

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?

CodePudding user response：

You should not mix insert..values and insert..select I would rewrite the insert as

INSERT INTO detail_transaction (id, id_item, count, total_price)
 select 2, 1, 10,  price * 10 
 FROM item 
WHERE item.ID = 1;

Although my choice would be a before insert trigger

DELIMITER $$
CREATE TRIGGER update_stock before INSERT  ON detail_transaction 
FOR EACH ROW
BEGIN
    set new.total_price = (
     select item.price * new.count 
     FROM item 
     WHERE item.ID = new.id
     );
END$$
DELIMITER ;

with an insert

INSERT INTO detail_transaction (id, id_item, count, total_price)
VALUES (2, 1, 10, null);

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.

CodePudding user response：

CREATE TABLE item (
  `id` INTEGER AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255),
  `price` INTEGER,
  `stock` INTEGER
);
INSERT INTO item VALUES
  ('1', 'Item_A', '15', '900'),
  ('2', 'Item_B', '9', '500');
SELECT * FROM item;

CREATE TABLE detail_transaction (
  `id` INTEGER AUTO_INCREMENT PRIMARY KEY,
  `id_item` INTEGER,
  `count` INTEGER,
  `total_price` INTEGER,
  FOREIGN KEY (`id_item`) REFERENCES `item` (`id`)
);
INSERT INTO detail_transaction VALUES
  ('1', '1', '5', '75');
SELECT * FROM detail_transaction;

id	name	price	stock
1	Item_A	15	900
2	Item_B	9	500

id	id_item	count	total_price
1	1	5	75

-- trigger which calculates total_price value
CREATE TRIGGER tr_bi_get_total_price
BEFORE INSERT ON detail_transaction 
FOR EACH ROW
  SET NEW.total_price = (
    SELECT NEW.`count` * item.price
    FROM item
    WHERE id = NEW.id_item
    );

-- trigger which adjusts stock value
CREATE TRIGGER tr_ai_update_stock_in_item
AFTER INSERT ON detail_transaction 
FOR EACH ROW
  UPDATE item
  SET stock = stock - NEW.count
  WHERE item.id = NEW.id_item;

INSERT INTO detail_transaction (id_item, `count`) VALUES (1, 10);
SELECT * FROM detail_transaction;
SELECT * FROM item;

id	id_item	count	total_price
1	1	5	75
2	1	10	150

id	name	price	stock
1	Item_A	15	890
2	Item_B	9	500

fiddle

PS. Each trigger contains only one statement. So neither BEGIN-END noк DELIMITER command needed.