Bank Transfer System

Build a stored procedure that transfers funds between accounts safely inside a transaction.

TransactionsStored ProceduresError Handling

Overview

Transferring money between two accounts is really two writes — subtract from one account, add to the other — and both must succeed or neither should happen. If the debit succeeds but something fails before the credit runs, money simply vanishes from the system. That failure mode is exactly what a database transaction exists to prevent: wrapping both updates in `BEGIN TRANSACTION` and `COMMIT TRANSACTION` makes them behave as one indivisible operation, and a `ROLLBACK TRANSACTION` inside a `TRY...CATCH` block undoes everything cleanly the moment anything goes wrong — including a simple business-rule failure like insufficient funds.

By the end of this tutorial you will have an `accounts` table protected by a `CHECK` constraint that makes a negative balance structurally impossible, a `ledger_transactions` table recording every debit and credit as an append-only history, and a `CREATE PROCEDURE` that performs an entire transfer — balance check, debit, credit, and two ledger rows — as one atomic transaction, using `THROW` to raise a clear custom error and roll back completely if the sender does not have enough funds.

What You'll Build

An `accounts` table with a `CHECK (balance >= 0)` constraint preventing negative balances.
A `ledger_transactions` table acting as an append-only ledger of every debit and credit.
A `transfer_funds` stored procedure wrapping a transfer in `BEGIN TRANSACTION` / `COMMIT TRANSACTION`.
A `TRY...CATCH` block with `ROLLBACK TRANSACTION` and `THROW` on insufficient funds.
Verification queries proving account balances and the ledger stay consistent after a transfer.

Prerequisites

CREATE TABLE basics in T-SQL — columns, data types, `NOT NULL`, and `DEFAULT` values.
Primary keys and foreign keys — how `FOREIGN KEY ... REFERENCES` links two tables.
Basic SELECT, INSERT, and UPDATE statements.
The general idea of a transaction — a group of statements that should all succeed or all fail together.
Basic stored procedure syntax — `CREATE PROCEDURE ... AS BEGIN ... END` and `@parameter` declarations.

Project Structure

`accounts` holds the current balance for every account — the single source of truth for "how much money does this account have right now." `ledger_transactions` never gets updated once a row is written; it only ever grows, recording history the way a real bank statement does. The stored procedure is what keeps the two in sync: every change to a balance in `accounts` happens inside the same transaction as the ledger rows that explain why.

Table	Purpose	Key Columns
accounts	Current balance per account	account_id (PK), balance (CHECK >= 0)
ledger_transactions	Append-only ledger of every debit/credit	ledger_id (PK), account_id (FK), transfer_id, entry_type, amount

Step 1: Create the Accounts Table

The `CHECK (balance >= 0)` constraint is the last line of defense against a negative balance — even if every other safeguard in the application layer somehow failed, SQL Server itself will refuse an `UPDATE` that would push `balance` below zero. `DECIMAL(12,2)`, not `FLOAT`, is used for money throughout this schema, because binary floating-point types cannot represent most decimal fractions exactly and will eventually produce off-by-a-cent errors a bank cannot tolerate.

CREATE TABLE accounts (
    account_id INT IDENTITY(1,1) PRIMARY KEY,      -- Surrogate key referenced by ledger_transactions.account_id
    owner_name NVARCHAR(100) NOT NULL,
    balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,   -- DECIMAL, not FLOAT: exact arithmetic, no rounding drift on money
    CONSTRAINT CK_accounts_balance CHECK (balance >= 0)  -- The database itself refuses to let balance go negative
);

Step 2: Create the Transactions Ledger

Every transfer produces exactly two ledger rows — a `DEBIT` on the sending account and a `CREDIT` on the receiving account — sharing the same `transfer_id` so they can always be found and matched back together later. `amount` is stored as a positive number in every row; whether it increased or decreased a balance is what `entry_type` records, not the sign of `amount`, which keeps every value in this append-only table simple to reason about.

CREATE TABLE ledger_transactions (
    ledger_id INT IDENTITY(1,1) PRIMARY KEY,       -- Own surrogate key for this individual ledger entry
    account_id INT NOT NULL,                       -- FK: which account this entry affects
    transfer_id UNIQUEIDENTIFIER NOT NULL,         -- Links the DEBIT and CREDIT rows from the same transfer together
    entry_type VARCHAR(6) NOT NULL,                -- 'DEBIT' or 'CREDIT' -- whether this entry decreased or increased the balance
    amount DECIMAL(12,2) NOT NULL,                 -- Always positive; entry_type says the direction, not the sign
    created_at DATETIME2 NOT NULL DEFAULT GETDATE(),
    CONSTRAINT FK_ledger_accounts FOREIGN KEY (account_id)
        REFERENCES accounts(account_id) ON DELETE NO ACTION,   -- History can't be orphaned by deleting an account
    CONSTRAINT CK_ledger_type CHECK (entry_type IN ('DEBIT', 'CREDIT')),  -- Only these two values are meaningful here
    CONSTRAINT CK_ledger_amount CHECK (amount > 0)              -- A zero or negative ledger entry would be meaningless
);

Step 3: Insert Sample Accounts

Two accounts to transfer money between — one with a healthy balance, and one deliberately low, so the stored procedure's insufficient-funds path can be demonstrated in Step 5.

INSERT INTO accounts (owner_name, balance) VALUES
('Vikram Singh', 5000.00),
('Ananya Desai', 150.00);

Step 4: Write a Fund Transfer Stored Procedure

`TRY...CATCH` is T-SQL's structured error-handling block: SQL Server runs everything inside `BEGIN TRY` and jumps straight to `BEGIN CATCH` the instant any statement fails, which is where the transaction gets rolled back and a clear error is raised with `THROW`. `SELECT ... WITH (UPDLOCK, ROWLOCK)` locks the sender's row for the rest of the transaction, so a second transfer running at the same time cannot read a stale balance and let two transfers both succeed against funds that only cover one of them. If the balance check fails, the procedure rolls back and throws; if it passes, both balance updates and both ledger rows commit together as one atomic unit.

CREATE PROCEDURE transfer_funds
    @from_account INT,
    @to_account INT,
    @amount DECIMAL(12,2)
AS
BEGIN
    SET NOCOUNT ON;   -- Suppresses the "N rows affected" messages so callers get clean results
    BEGIN TRY
        BEGIN TRANSACTION;   -- Everything from here to COMMIT/ROLLBACK happens as one atomic unit

        DECLARE @current_balance DECIMAL(12,2);
        DECLARE @transfer_id UNIQUEIDENTIFIER = NEWID();  -- One shared ID linking this transfer's two ledger rows

        -- WITH (UPDLOCK, ROWLOCK) locks this row until COMMIT/ROLLBACK, so a concurrent
        -- transfer from the same account can't read the balance before this one finishes
        SELECT @current_balance = balance
        FROM accounts WITH (UPDLOCK, ROWLOCK)
        WHERE account_id = @from_account;

        IF @current_balance IS NULL
        BEGIN
            THROW 51000, 'Source account does not exist', 1;
        END

        IF @current_balance < @amount
        BEGIN
            THROW 51001, 'Insufficient funds for this transfer', 1;
        END

        -- Debit the sender
        UPDATE accounts SET balance = balance - @amount WHERE account_id = @from_account;
        -- Credit the receiver
        UPDATE accounts SET balance = balance + @amount WHERE account_id = @to_account;

        INSERT INTO ledger_transactions (account_id, transfer_id, entry_type, amount)
        VALUES (@from_account, @transfer_id, 'DEBIT', @amount);

        INSERT INTO ledger_transactions (account_id, transfer_id, entry_type, amount)
        VALUES (@to_account, @transfer_id, 'CREDIT', @amount);

        COMMIT TRANSACTION;   -- Both balance updates and both ledger rows become permanent together
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;   -- Undo everything; nothing written so far takes effect

        THROW;   -- Re-raise the original error so the caller sees exactly what went wrong
    END CATCH
END;

Why the balance CHECK constraint still matters here

The `IF @current_balance < @amount` check stops most overdrafts, but the table-level `CHECK (balance >= 0)` constraint from Step 1 is what guarantees it structurally — even a future bug in this procedure, or a totally different piece of code updating `accounts` directly, still cannot push a balance negative.

Step 5: Execute the Procedure and Verify the Ledger

A successful transfer from Vikram to Ananya, followed by a failed one attempted from Ananya's now-smaller balance against too large an amount — showing both the commit path and the rollback-and-throw path in the same procedure.

EXEC transfer_funds @from_account = 1, @to_account = 2, @amount = 500.00;  -- Vikram sends 500.00 to Ananya: succeeds

SELECT account_id, owner_name, balance FROM accounts ORDER BY account_id;

account_id	owner_name	balance
1	Vikram Singh	4500.00
2	Ananya Desai	650.00

EXEC transfer_funds @from_account = 2, @to_account = 1, @amount = 10000.00;  -- Ananya tries to send far more than her balance: rejected

Error Raised by THROW

Click Run to see what this code prints.

Complete Schema

The full schema and stored procedure, ready to run top to bottom.

CREATE TABLE accounts (
    account_id INT IDENTITY(1,1) PRIMARY KEY,
    owner_name NVARCHAR(100) NOT NULL,
    balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    CONSTRAINT CK_accounts_balance CHECK (balance >= 0)
);

CREATE TABLE ledger_transactions (
    ledger_id INT IDENTITY(1,1) PRIMARY KEY,
    account_id INT NOT NULL,
    transfer_id UNIQUEIDENTIFIER NOT NULL,
    entry_type VARCHAR(6) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    created_at DATETIME2 NOT NULL DEFAULT GETDATE(),
    CONSTRAINT FK_ledger_accounts FOREIGN KEY (account_id)
        REFERENCES accounts(account_id) ON DELETE NO ACTION,
    CONSTRAINT CK_ledger_type CHECK (entry_type IN ('DEBIT', 'CREDIT')),
    CONSTRAINT CK_ledger_amount CHECK (amount > 0)
);
GO

CREATE PROCEDURE transfer_funds
    @from_account INT,
    @to_account INT,
    @amount DECIMAL(12,2)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        DECLARE @current_balance DECIMAL(12,2);
        DECLARE @transfer_id UNIQUEIDENTIFIER = NEWID();

        SELECT @current_balance = balance
        FROM accounts WITH (UPDLOCK, ROWLOCK)
        WHERE account_id = @from_account;

        IF @current_balance IS NULL
        BEGIN
            THROW 51000, 'Source account does not exist', 1;
        END

        IF @current_balance < @amount
        BEGIN
            THROW 51001, 'Insufficient funds for this transfer', 1;
        END

        UPDATE accounts SET balance = balance - @amount WHERE account_id = @from_account;
        UPDATE accounts SET balance = balance + @amount WHERE account_id = @to_account;

        INSERT INTO ledger_transactions (account_id, transfer_id, entry_type, amount)
        VALUES (@from_account, @transfer_id, 'DEBIT', @amount);

        INSERT INTO ledger_transactions (account_id, transfer_id, entry_type, amount)
        VALUES (@to_account, @transfer_id, 'CREDIT', @amount);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;

        THROW;
    END CATCH
END;

Sample Queries

Two queries that prove the ledger and the account balances agree with each other — which is exactly what the atomic procedure guarantees will always be true.

-- Full transaction history for one account, most recent first
SELECT ledger_id, transfer_id, entry_type, amount, created_at
FROM ledger_transactions
WHERE account_id = 2
ORDER BY created_at DESC;

ledger_id	transfer_id	entry_type	amount	created_at
2	3F2504E0-...	CREDIT	500.00	2026-08-09 10:15:00

-- Reconcile: an account's stored balance should always equal its starting balance
-- plus every CREDIT minus every DEBIT recorded against it in the ledger
SELECT
    a.account_id,
    a.owner_name,
    a.balance AS stored_balance,
    COALESCE(SUM(CASE WHEN t.entry_type = 'CREDIT' THEN t.amount ELSE -t.amount END), 0) AS net_ledger_movement
FROM accounts a
LEFT JOIN ledger_transactions t ON a.account_id = t.account_id
GROUP BY a.account_id, a.owner_name, a.balance;

account_id	owner_name	stored_balance	net_ledger_movement
1	Vikram Singh	4500.00	-500.00
2	Ananya Desai	650.00	500.00

Extend This Project

Add an `account_type` column (`'checking'`/`'savings'`) and enforce different overdraft rules per type.
Add a `daily_transfer_limit` column to `accounts` and check it inside `transfer_funds` before allowing a transfer.
Set the transaction isolation level explicitly with `SET TRANSACTION ISOLATION LEVEL SERIALIZABLE` for stricter consistency.
Add a composite index on `ledger_transactions(account_id, created_at)` to speed up per-account statement queries.
Add an audit trigger that writes a row to a separate `audit_log` table on every call to `transfer_funds`.

Summary

You built a schema where a `CHECK` constraint makes a negative balance structurally impossible, and a stored procedure makes a two-account transfer atomic: `BEGIN TRANSACTION` groups the balance check, both updates, and both ledger inserts into one unit, `COMMIT TRANSACTION` makes them permanent together, and a `TRY...CATCH` block with `ROLLBACK TRANSACTION` and `THROW` undoes everything and raises a clear error the moment the balance check fails. That combination — a business-rule check, a row lock to prevent a race with concurrent transfers, and an all-or-nothing commit — is the same shape every real payment or transfer system relies on, whether it moves money, inventory, or any other value that must never be created or destroyed by a half-finished operation.