CODEWITHSUNDEEP
mysqlsqltriggers
Namit
Namit

Reputation: 1322

update trigger using mysql

I have two tables:

Sales_Order

Order_ID       Buyer_ID     Emp_ID     Status (ENUM)
   1              2           3          P

Sales_Order_Details

Detail_ID      Order_ID     Quantity    Item_ID     Balance
   1              1            10          3           10
   2              1            20          6            5

I am trying to create a trigger which looks for updates on the Sales_Order_Details Table and sees that if Balance of any Detail_ID changes, it looks for all the Balance with the Same Order_ID, and if all all 0 then change the status of that Order in Sales_Order table to 'C'.

I am new to triggers and have been reading a few things, but i am quite confused in using them.

CREATE TRIGGER flag_order_complete AFTER UPDATE ON Sales_Order_Details
FOR EACH ROW
BEGIN 
IF Balance = '0' THEN
UPDATE Sales_Order SET Status 'C' where Order_ID = old.Order_ID
END

Now this probably is horribly wrong. It will be great if anyone could help me through!

Upvotes: 0

Views: 438

Answers (1)

David Faber
David Faber

Reputation: 12495

Well, there are some obvious syntactical issues. You might try something like this:

CREATE TRIGGER flag_order_complete AFTER UPDATE ON Sales_Order_Details
FOR EACH ROW
BEGIN
  DECLARE total_balance INT(10);

  SELECT SUM(Balance) INTO total_balance
    FROM Sales_Order_Details
   WHERE Order_ID = new.Order_ID;

  IF (total_balance = 0) THEN
    UPDATE Sales_Order SET Status = 'C' where Order_ID = new.Order_ID;
  END IF;
END;

What this doesn't address is the fact that individual rows in Sales_Order can be updated multiple times because of the FOR EACH ROW clause (e.g., if two rows with the same Order_ID are updated at the same time, the matching record in Sales_Order will be updated twice if the total balance is zero). You can write a more complicated query with a cursor looping over the updated records in order to avoid this.

Upvotes: 1

Related Questions