Solved

Converting MS SQL Trigger to MySQL

Posted on 2014-09-11
4
598 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 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now