Search code examples
mysqlmysql-error-1064

Mysql Trigger Prolem

I got 2 Table which is Transaction and Product

DELIMITER $$

CREATE TRIGGER total_value
  AFTER UPDATE ON product
  FOR EACH ROW
BEGIN
    UPDATE transaction
       SET transaction.TotalPrice = product.ProductPrice * Transaction.OrderedQty   
END;

DELIMITER ;

But it Shows Unexpection Error

Im Sorry Actually My Problem is I want to insert a Total Value in my Transaction to calculate the qty and product price where product price is from another table which is product table and My Problem is I Don't know a Right Syntax for that :(

Here is my Transaction

Here is My Product

Solution

  • I think you want to do this and upodate with the new Productprice.

    DELIMITER $$

CREATE TRIGGER total_value
  AFTER UPDATE ON product
  FOR EACH ROW
BEGIN
    UPDATE transaction tr
       SET tr.TotalPrice = NEW.ProductPrice * tr.OrderedQty
          Where tr.ProductID = NEW.PRoduktID;  
END;

DELIMITER ;

    So it only Updates the column and totalprice when the productID si equal