Question 20234 in Mysql Question posted in
The official MySQL documentation can be found here.

Create MySQL triggers

I need to write a trigger so that it counts the total purchase amount(total_price) in the purchase_data table, that is, multiplies the quantity(product_count) from the purchase_data table by the price of the product(product_price) from the product table. It is necessary that after inserting or updating values in the purchase_data table, the total_price field is also updated, I hope I explained it clearly

product table
purchase_data table

CREATE DEFINER=`root`@`localhost` TRIGGER `calculate_total_price` AFTER INSERT ON `purchase_data` FOR EACH ROW BEGIN
UPDATE purchase_data pd
SET total_price = (
    SELECT p.product_price * NEW.product_count 
    FROM product p
    WHERE p.product_id = NEW.product_id
)
WHERE pd.purchase_id = NEW.purchase_id;
END

I tried to create some triggers but I have a constant error
Can't update table 'purchase_data' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. I tried everything and failed to overcome it

code table purchase_data

CREATE TABLE `Purchase_data` (
  `purchasedata_id` INT NOT NULL AUTO_INCREMENT,
  `purchase_id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `product_count` INT NOT NULL,
  `total_price` DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (`purchasedata_id`),
  INDEX `Purchase_idx` (`purchase_id` ASC) VISIBLE,
  INDEX `Product_idx` (`product_id` ASC) VISIBLE,
  CONSTRAINT `Purchase`
    FOREIGN KEY (`purchase_id`)
    REFERENCES `DB_OnlineTrade`.`Purchase` (`purchase_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `Product`
    FOREIGN KEY (`product_id`)
    REFERENCES `DB_OnlineTrade`.`Product` (`product_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)

code table product

CREATE TABLE `Product` (
  `product_id` INT NOT NULL AUTO_INCREMENT,
  `product_name` VARCHAR(45) NOT NULL,
  `date_pub` DATETIME NOT NULL,
  `publisher_id` INT NOT NULL,
  `category_id` INT NOT NULL,
  `product_price` DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (`product_id`),
  INDEX `Categoty_idx` (`category_id` ASC) VISIBLE,
  INDEX `Publisher_idx` (`publisher_id` ASC) VISIBLE,
  CONSTRAINT `Publisher`
    FOREIGN KEY (`publisher_id`)
    REFERENCES `DB_OnlineTrade`.`Publisher` (`publisher_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `Category`
    FOREIGN KEY (`category_id`)
    REFERENCES `DB_OnlineTrade`.`Category` (`category_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)

table product:

product_id   product_cost
1                100  
2                250
3                370
..

table purchase_data:

purchasedata_id   product_count product_id  total_price
1                       2            3         370*2
2                       1            2         250*1
3                       3            1         100*3
..

I need a trigger that will calculate total_price after inserting or updating data. Omitted seemingly unnecessary columns in the tables.

Tags:

2

Answers


  1. 0

    Triggers have limited utility for updating tables other than the one for which they fire. It’s hard to tell you more without seeing the code foir all your triggers, and the SQL statements which fire them.

    Login or Signup to reply.
  2. 0 
    CREATE DEFINER=`root`@`localhost` 
TRIGGER `calculate_total_price` 
BEFORE INSERT ON `purchase_data` 
FOR EACH ROW 
SET NEW.total_price = (
    SELECT product_price * NEW.product_count 
    FROM product
    WHERE product_id = NEW.product_id
    );

    And the same for BEFORE UPDATE.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search