Solved

Converting MS SQL Trigger to MySQL

Posted on 2014-09-11
4
662 Views
Last Modified: 2014-09-11
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
0
Comment
Question by:Brian Covington
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
ID: 40318201
ok, so a couple of things. You cant have a trigger in mysql that attaches to both insert and update, so first thing to do is make a stored procedure.

I think you can shorten what you have to this:
CREATE PROCEDURE `order_details_total_cost` (_id int)
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

Open in new window


Incidentally, I don't really understand your query. I think you can shorten it quite a bit. You are linking to the inserted and deleted tables to get the fk for orderid, but then you are restricting it to the order id that is being updated.  Could you just do this?

UPDATE Orders
SET
  GrandTotalCost = (SELECT sum(TotalCost) from order_details WHERE fkOrderID=OrderID);

If you have some sample data, we can test it.

After that, you have to make two triggers:

CREATE TRIGGER `order_details_AINS` AFTER INSERT ON `order_details` FOR EACH ROW
call order_details_total_cost (NEW.OrderID);

CREATE TRIGGER `order_details_AUPD` AFTER UPDATE ON `order_details` FOR EACH ROW
call order_details_total_cost (NEW.OrderID);

Open in new window

0
 

Author Comment

by:Brian Covington
ID: 40318336
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?
0
 

Author Comment

by:Brian Covington
ID: 40318355
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!
0
 

Author Closing Comment

by:Brian Covington
ID: 40318453
Excellent solution.  Thank you!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question