MySQL trigger when field change value

Question

I have 3 table in database:

product

CREATE TABLE `product` (
`product_id` int(11) NOT NULL,
`product_name` varchar(50) NOT NULL,
`product_stock` int(11) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

transaction

CREATE TABLE `transaction` (
`transaction_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`transaction_date` datetime NOT NULL,
`transaction_status` ENUM('pending','process','cancel') NOT NULL DEFAULT 'pending'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

transaction_details

CREATE TABLE `transaction_details` (
`transaction_id` int(11) NOT NULL,
`product_id` int(11) NOT NULL,
`qty` int(11) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

if transaction_status in transaction table changed to "cancel" how to update product_stock in product table based on qty in transaction_details table using trigger

Nick · Accepted Answer · 2019-01-30 05:17:26Z

2

This trigger should do what you want. After an UPDATE on transaction it updates the stock of all products in the transaction (by using JOINs on all three tables to find the relevant products for the transaction):

CREATE TRIGGER update_stock 
AFTER UPDATE ON transaction
FOR EACH ROW
BEGIN
  IF NEW.transaction_status = 'cancel' THEN
    UPDATE transaction t
    JOIN transaction_details td ON td.transaction_id = NEW.transaction_id
    JOIN product p ON p.product_id = td.product_id
    SET p.product_stock = p.product_stock + td.qty;
  END IF;
END

Demo on dbfiddle

answered Jan 30, 2019 at 5:17

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arif Nur Rohman Over a year ago

thanks for the answer, but I'm facing mysql error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 9

Collectives™ on Stack Overflow

MySQL trigger when field change value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related