#1
  1. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,381
    Rep Power
    594

    Trigger to update row in same table


    I have been going in circles with this for a month now and have not been able to come up with a satisfactory solution including numerous searches. The closest I came was to set a value in a semaphore table from my trigger then set a timer to delete that semaphore value. Upon deletion, I update the row from the previous trigger. The problem is one of timing. If another query is done against that row before it gets updated the values will be wrong.

    Can someone please suggest how I can set up a trigger or set of triggers that will update a row in the same table safely. Surely I cannot be the only one to ever encounter this problem so there must be a solution somewhere. TIA.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,126
    Rep Power
    4274
    Originally Posted by gw1500se
    Surely I cannot be the only one to ever encounter this problem so there must be a solution somewhere. TIA.
    how about a redesign that doesn't require the update at all?

    that'd be the easiest solution

    because you simply cannot update the same table that the trigger is defined on --
    Originally Posted by da manual
    Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,381
    Rep Power
    594
    Thanks for the reply but unfortunately that is not an option so that is not the easiest way. This is a 3rd party application and database schema so that there is no reasonable way to redesign.

    Since this seems to be an issue unique to MySQL (other database engines seem quite capable of handling this) surely there must be some solution that someone smarter than me has come up with.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,126
    Rep Power
    4274
    Originally Posted by gw1500se
    This is a 3rd party application and database schema so that there is no reasonable way to redesign.
    pro tip: mention shít like this in your first post, please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,381
    Rep Power
    594
    Sorry but I figured if I could redesign, I wouldn't have needed to ask the question in the first place.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,126
    Rep Power
    4274
    Originally Posted by gw1500se
    Sorry but I figured if I could redesign, I wouldn't have needed to ask the question in the first place.
    yeah, but we didn't know that, lol
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,126
    Rep Power
    4274
    how about START TRANSACTION, two UPDATEs, and COMMIT?

    this will prevent race conditions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,381
    Rep Power
    594
    I haven't used transactions so I am not too familiar with them. I need to understand the flow a bit. I am doing a trigger on an update to row A. I need to take the data from a column on that update and use it to update row B.

    So the trigger would be something like this?

    1) start transaction
    2) update row A
    3) compute values for row B from NEW data from row A
    4) update row B
    5) commit
    6) end transaction

    Doesn't that result in the same problem or am I missing something special about transactions? It seems to me the update row B would result in a new trigger and thus recursion?
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,126
    Rep Power
    4274
    you would not use a trigger in the transaction

    1) start transaction
    2) update row A
    3) update row B
    4) commit

    also, check this --
    Originally Posted by da manual
    Many APIs used for writing MySQL client applications (such as JDBC) provide their own methods for starting transactions that can (and sometimes should) be used instead of sending a START TRANSACTION statement from the client. See Chapter 26, APIs and Libraries, or the documentation for your API, for more information.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,381
    Rep Power
    594
    I have no clue how to start the transaction without a trigger. How do I detect that a row is being updated without one?
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,126
    Rep Power
    4274
    Originally Posted by gw1500se
    How do I detect that a row is being updated without one?
    go to the source code and find the UPDATE statement that would've invoked the trigger, and replace it with the transaction
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,381
    Rep Power
    594
    Unfortunately there is no THE UPDATE statement, there are many and it is not always clear from the code what tables are being updated when (lots of programatically generated updates). While I am not an expert, I am not a novice either and I keep coming back to using a trigger. If I could have figured out a way to modify the code (PHP), I would have.

    As unlikely as it is, there seems no way to do what I need (in MySQL) but it is stretching credibility that I am the first person to ever encounter this problem.
    Last edited by gw1500se; August 14th, 2013 at 11:11 AM.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  24. #13
  25. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Since this:
    Originally Posted by gw1500se
    This is a 3rd party application and database schema so that there is no reasonable way to redesign.
    indicates that this is a bought application where you want to alter it's functionality by adding a trigger that modifies the data in the database in the background.

    This makes me find your remark a bit odd:
    Originally Posted by gw1500se
    As unlikely as it is, there seems no way to do what I need (in MySQL) but it is stretching credibility that I am the first person to ever encounter this problem.
    Because I say that you are solely encountering this problem because you are trying to wrestle in a modification of how the application functions by adding a trigger instead of rewriting the application.

    Adding triggers that modifies the data in an applications database should be considered the same as modifying the application code itself.
    How can you know that modifying this value without the applications knowledge is safe? Maybe the application caches these values under certain circumstances and won't notice your changes in the database until later?
    /Stefan
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,381
    Rep Power
    594
    The problem is the, IMO, unnecessary complexity and poor programming practices of the code I am stuck with. While I have the source it is just too difficult to try to modify it to satisfy this requirement compared to a trigger. I have sufficient understanding of the app to know the change I need to make is safe.

    The only good thing is that this code supports multiple database engines and while I prefer MySQL for other reasons, I am going to have to look at other engines that do not have this problem with triggers.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.

IMN logo majestic logo threadwatch logo seochat tools logo