Solved

Converting MS SQL Trigger to MySQL

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Results to Excel File 18 73
SQL Pivot table 2 42
SQL Server Trigger 8 33
how to make geography query faster?  SQL 7 41
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

685 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