We have a table that maintains the account balance with the transaction recording in that table. That is, the most recent row has account balance.
While recording a comeback, we want to make sure that the balance can never go negative. Our proposed solution is something like this:
`txns' (` Select INSERT in account_ID, `r_e_business',` tnn_price`, `new_belance`,` amount`, `statement`) Account_id, t.new_balance, $ txn_type, t.new_balance - $ amount, $ amount, $ description `txns`t WHERE t.account_id = '$ account' and (select new_balance FROM txns WHERE account_id = '$ account' ORDER BY Txn_id desc limit 1) & gt; = $ Amount txn_id desc LIMIT by ORDER 1; But the endive subquery (we had a subquery performance problems on one if we are a bit concerned about the performance of the previous project). Any developers here are not SQL experts. No extra clauses in deposits. This is all on MySQL 5.0
I inquired about apology Can not say anything about the performance of, but you will stop the case of 'new_bill' If the 'New_balance' is less than the $ amount, but it can still work, then in case I am ineligible to include an ineligible.). < P> How to create a trigger for details.
Actually you put a check, if the negative of the NEW.new_balance triggers before the beginning, if you trigger the trigger and the "INSERT-query" Stop action ", an intentional error in execution Will use. See opinion mentioned page comments.
Update: A little bit smaller (pretend to install MySQL on my house).
There is a problem writing for the second time in DB for each value of entry of money
Perhaps it would be appropriate to switch to a stored proc. Or anyone else has a better idea, I'm not much into DB :)
Create a database of triggatests; Connect triggered; Create a table transfer (Account Smallling Unsolved Faucet, Zodiac INT, New Ibal Not INT) Engine = INNODB; Create tablet stop action (entry CRAR (20) tap, dummy smalling, unique (`entry`); Insert the stop action (`entry ') VALUES (' stop '); DELIMITER # Select the trigger ('entry') before entering the INSERT for the transfer of each trause when NEW.new_balance & lt; 0 then 'stop' ELSE 'none' END; Remove from stoppoint where entry! = 'Stop'; End; # Create Triggers Non-NG_Update Enter the stop action (`entry ') before updating each roby transfer when new. NUI Billions & lt; 0 then 'stop' ELSE 'none' END; Remove from stoppoint where entry! = 'Stop'; End; # DELIMITER; Log transfer (`account`,` amount`, `new_balance`) value (1, 1000, 1000); Log transfer (`account`,` amount`, `new_balance`) value (1, -1000, 0); INSERT (`account`,` amount`, `new_balance`) for the transfer value (1, -1000, -1000); Sign In Transfer (`account`,` amount`, `new_balance`) value (1, 10, 20); Select version (); Drop Database Trigestate;
Perhaps it will suit you, my output for INSERT-Lines is:
mysql> Log transfer (`account`,` amount`, `new_balance`) value (1, 1000, 1000); The query is fine, 1 row affected (0.03 seconds) mysql & gt; Log transfer (`account`,` amount`, `new_balance`) value (1, -1000, 0); The query is fine, 1 line affected (0.02 seconds) mysql & gt; INSERT (`account`,` amount`, `new_balance`) for the transfer value (1, -1000, -1000); Error 1062 (23000): The key to duplicate entry 'stop' 1 mysql & gt; Sign In Transfer (`account`,` amount`, `new_balance`) value (1, 10, 20); The query is fine, 1 line affected (0.02 seconds) mysql & gt; Select version (); + --------------------- + | Version () | + --------------------- + | 5.0.67-Community-NT | + --------------------- + 1 line set (0.00 seconds)
Comments
Post a Comment