update trigger using mysql

Question

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!

David Faber · Accepted Answer · 2012-03-26 18:13:54Z

1

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.

answered Mar 26, 2012 at 18:13

David Faber

12.5k2 gold badges33 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Namit Over a year ago

Thanks for that, being new to triggers could you please guide me to a proper website (if you know any) so that i can read up on it.

Collectives™ on Stack Overflow

update trigger using mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related