Solved

Converting MS SQL Trigger to MySQL

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

776 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