Facebook
Twitter
LinkedIn
Instagram
Whatsapp
Call Now
Quick Inquiry

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).


9 0

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