asked on

Converting MS SQL Trigger to MySQL

I am trying to convert a MS SQL trigger to MySQL 5.6 (see trigger below). I am migrating an existing database over to MySQL. I am new to MySQL and the syntax is obviously a little different and I keep running into errors. I am under pressure to get this done which isn't helping. Could someone please look over the syntax and let me know if this is a minor fix or if the trigger needs to be re-written?

MS SQL Trigger:

CREATE TRIGGER order_details_total_cost ON order_details FOR INSERT,
UPDATE,
DELETE AS
BEGIN
      UPDATE Orders
SET GrandTotalCost = tots.total
FROM
      (
            SELECT
                  insdel.fkOrderID,
                  isnull(
                        sum(order_details.TotalCost),
                        0
                  ) AS total
            FROM
                  (
                        SELECT
                              fkOrderID
                        FROM
                              inserted
                        UNION
                              SELECT
                                    fkOrderID
                              FROM
                                    deleted
                  ) insdel
            LEFT OUTER JOIN order_details ON insdel.fkOrderID = order_details.fkOrderID
            GROUP BY
                  insdel.fkOrderID
      ) tots
WHERE
      OrderID = tots.fkOrderID
END

ASKER CERTIFIED SOLUTION

mankowitz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brian Covington

ASKER

Thank you! This is very close. Your shortened procedure and triggers was a great solution. The trigger is firing and the procedure is updating the total cost of the order correctly, however it is updating that value to every order in the database. So if the order total is $57, every order total will now be $57. I used the triggers above, although I changed (NEW.OrderID) to (NEW.fkOrderID)

For the procedure, I used:

"UPDATE Orders SET GrandTotalCost = (SELECT sum(TotalCost) from order_details WHERE fkOrderID=_id);" adding the "_id" to the where clause... which obviously wasn't correct.

How do I pass the specific OrderID into the procedure?

Brian Covington

ASKER

duh... sorry, I needed an additional where clause in the procedure. This appears to be working now. I will test it out and return to document the change accept your solution in a few. Thank you!

Brian Covington

ASKER

Excellent solution. Thank you!