Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Converting MS SQL Trigger to MySQL

Posted on 2014-09-11
4
Medium Priority
?
854 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
  • 3
4 Comments
 
LVL 24

Accepted Solution

by:
mankowitz earned 2000 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

578 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