alexa

Achieve simple "payment transaction" in MYSQL (or any rdbms) in SQL ?

Achieve simple "payment transaction" in MYSQL (or any rdbms) in SQL ?
  1. Add CHECK constraint which checks that the balance value is not negative:
 ALTER TABLE tablename
    ADD CHECK (balance >= 0); 
  1. Perform the transaction as solid statement:
 UPDATE tablename
SET balance = CASE account_id WHEN @sender_id   THEN balance - @transfer
                              WHEN @receiver_id THEN balance + @transfer
                              ELSE balance
                              END
WHERE account_id IN (@sender_id, @receiver_id); 

The whole transfer will either be performed successfully (and both accounts will be updated) or will produce an error 3819 (and none account will be updated).


297 0
7

Write a Comments


* Be the first to Make Comment

GoodFirms Badge
GoodFirms Badge

Fix Your Meeting With Our SEO Consultants in India To Grow Your Business Online

Facebook
Twitter
LinkedIn
Instagram
Whatsapp
Call Now
Quick Inquiry