14. Money Transfer

CREATE PROCEDURE usp_transfer_money(from_account_id INT, to_account_id INT, amount DECIMAL(19,4))

BEGIN
START TRANSACTION;

        IF NOT EXISTS (SELECT a.id FROM accounts a WHERE a.id = from_account_id) THEN ROLLBACK;
        ELSEIF NOT EXISTS (SELECT a.id FROM accounts a WHERE a.id = to_account_id) THEN ROLLBACK;
        ELSEIF amount < 0 THEN ROLLBACK;
        ELSEIF from_account_id = to_account_id THEN ROLLBACK;
        ELSEIF (SELECT a.id FROM accounts a WHERE a.id = from_account_id AND a.balance - amount < 0) THEN ROLLBACK;
        ELSE
            UPDATE accounts SET balance = balance - amount WHERE id = from_account_id;
            UPDATE accounts SET balance = balance + amount WHERE id = to_account_id;
        END IF;
        COMMIT;

END;