Want a conditional insert function? Is it possible?
I'm writing a back-end script that queries a large number of records and works through them over a period of an hour or so.
Each record is processed and may generate a financial transaction that needs to be added to the customer's account. The customer's account balance is returned in the original result set, but they may have several transactions in the processing queue and the sum of those transactions may allow their account balance to go negative if I rely on the account balance in the result set.
What I want to do is create a statement that only inserts the new transaction if the current account balance is greater than the transaction amount. I want to know if the insert happened or not so I can take appropriate action.
If such a function exists, how would I determine whether the insert happened or not?
THEN(INSERT INTO transactions (txn_amount, timestamp, account_balance, user_id) VALUES($txn_amount, NOW(), $new_account_balance, $user_id))
I'm trying to avoid doing a database query to check the balance for each transaction, as it would likely bog the server down.