MySQL - After Insert Trigger that updates two tables?

Question

I have a problem with a MySQL Trigger. I have 3 tables Customers, Products and Sales. In Sales I reference customer and product and I want to update the some counts on Products and Customers after a new sale is inserted. The following trigger fails to update both tables... I cannot figure out what I am doing wrong.

DELIMITER $
CREATE TRIGGER OnSalesInsert AFTER INSERT ON Sales
FOR EACH ROW BEGIN
 UPDATE Products SET Products.sold=Products.sold+NEW.amount WHERE Products.id=NEW.product_id;
 UPDATE Customers SET Customers.amount=Customers.amount+NEW.amount WHERE Customers.id=NEW.customer_id;
END $
DELIMITER ;

Solution

Try this:

DELIMITER $$

CREATE
    /*!50017 DEFINER = 'root'@'%' */
    TRIGGER `OnSalesInsert` BEFORE INSERT ON `Sales` 
    FOR EACH ROW BEGIN

       UPDATE Products 
       SET sold = sold + new.amount 
       WHERE id = new.product_id; 

       UPDATE Customers 
       SET amount = amount + new.amount 
       WHERE id = new.customer_id;
    END;
$$

DELIMITER ;

Comments

[ianaya89]

Try to add a brief explanation, it will help a lot.