CODEWITHSUNDEEP
mysqltriggersinsert-update
user1925772
user1925772

Reputation: 107

MySQL - After Insert Trigger that updates two tables?

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 ;

Upvotes: 2

Views: 14271

Answers (2)

Tapasya
Tapasya

Reputation: 1

DELIMITER $$
create trigger UpdateAvail after insert on product_details
for each row
Begin
 Declare a1 INT;
Declare d1 VARCHAR(1);
Declare d2 VARCHAR(100);
Select count(0) INTO a1 from prod_available where P_Id=new.P_Id;
Select P_Name,P_Brand INTO d1,d2 from product where P_Id=new.P_Id;
IF a1>0 THEN
Update prod_available set P_quantity=P_quantity+new.quantity where P_Id=new.P_Id;
ELSE
insert into prod_available (P_Id,P_Name,P_Brand,P_quantity)   values                     (new.P_Id,d1,d2,new.quantity);
  END IF;
  END;
  $$
DELIMITER ;

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29051

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 ;

Upvotes: 1

Related Questions