CODEWITHSUNDEEP
mysqldatabasetriggersdatabase-trigger
Arif Nur Rohman
Arif Nur Rohman

Reputation: 195

MySQL trigger when field change value

I have 3 table in database:

  1. 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;

  2. 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;

  3. 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

Upvotes: 1

Views: 389

Answers (1)

Nick
Nick

Reputation: 147216

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

Upvotes: 2

Related Questions