Sql-server – Writing a simple bank schema: How should I keep the balances in sync with their transaction history

Often with operations like running totals it is actually more efficient to use a cursor than some of the other methods. Don't be afraid of using a cursor unless you are seeing noticeable performance issues.

In SQL Server 2012 there are new window functions that make this better, but obviously you can't use those. There is a quirky update approach that "works" but the syntax isn't officially supported and might not work post-SQL Server 2012 - it may someday become illegal syntax and there is never any guarantee about how the ordering works. The typical set-based approaches don't scale well - with a cursor it is often better because you only have to scan each row once, whereas with a set-based solution the scanning grows non-linearly. I wish I could show you the work I've been doing on this, but the publication date of the blog post that will reveal it all is still unknown.

At the very least when you declare a cursor use:

DECLARE ... CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY

This also might be useful reading:

• Bad habits to kick: Thinking a while loop isn't a cursor

1. Does the trigger follow the relational database's ACID principle? Is there any chance an insert might be committed but the trigger fail?

This question is partly answered in a related question you linked to. Trigger code is executed in the same transactional context as the DML statement that caused it to fire, preserving the Atomic part of the ACID principles you mention. The triggering statement and trigger code both succeed or fail as a unit.

The ACID properties also guarantee the whole transaction (including the trigger code) will leave the database in a state that does not violate any explicit constraints (Consistent) and any recoverable committed effects will survive a database crash (Durable).

Unless the surrounding (perhaps implicit or auto-commit) transaction is running at the SERIALIZABLE isolation level, the Isolated property is not automatically guaranteed. Other concurrent database activity could interfere with the correct operation of your trigger code. For example, the account balance could be changed by another session after you read it and before you update it - a classic race condition.

2. My IF and UPDATE statements look strange. Is there any better way to update the correct [Account] row?

There are very good reasons the other question you linked to does not offer any trigger-based solutions. Trigger code designed to keep a denormalized structure synchronized can be extremely tricky to get right and test properly. Even very advanced SQL Server people with many years of experience struggle with this.

Maintaining good performance at the same time as preserving correctness in all scenarios and avoiding problems like deadlocks adds extra dimensions of difficulty. Your trigger code is nowhere close to be robust, and updates the balance of every account even if only a single transaction is modified. There are all sorts of risks and challenges with a trigger-based solution, which makes the task deeply unsuitable for someone relatively new to this technology area.

To illustrate some of the problems, I show some sample code below. This is not a rigorously-tested solution (triggers are hard!) and I am not suggesting you use it as anything other than a learning exercise. For a real system, the non-trigger solutions have important benefits, so you should carefully review the answers to the other question, and avoid the trigger idea completely.

Sample tables

CREATE TABLE dbo.Accounts
(
    AccountID integer NOT NULL,
    Balance money NOT NULL,

    CONSTRAINT PK_Accounts_ID
    PRIMARY KEY CLUSTERED (AccountID)
);

CREATE TABLE dbo.Transactions
(
    TransactionID integer IDENTITY NOT NULL,
    AccountID integer NOT NULL,
    Amount money NOT NULL,

    CONSTRAINT PK_Transactions_ID
    PRIMARY KEY CLUSTERED (TransactionID),

    CONSTRAINT FK_Accounts
    FOREIGN KEY (AccountID)
    REFERENCES dbo.Accounts (AccountID)
);

Preventing TRUNCATE TABLE

Triggers are not fired by TRUNCATE TABLE. The following empty table exists purely to prevent the Transactions table being truncated (being referenced by a foreign key prevents table truncation):

CREATE TABLE dbo.PreventTransactionsTruncation
(
    Dummy integer NULL,

    CONSTRAINT FK_Transactions
    FOREIGN KEY (Dummy)
    REFERENCES dbo.Transactions (TransactionID),

    CONSTRAINT CHK_NoRows
    CHECK (Dummy IS NULL AND Dummy IS NOT NULL)
);

Trigger Definition

The following trigger code ensures only necessary account entries are maintained, and uses SERIALIZABLE semantics there. As a desirable side-effect, this also avoids the incorrect results that might result if a row-versioning isolation level is in use. The code also avoids executing the trigger code if no rows were affected by the source statement. The temporary table and RECOMPILE hint are used to avoid trigger execution plan problems caused by inaccurate cardinality estimates:

CREATE TRIGGER dbo.TransactionChange ON dbo.Transactions 
AFTER INSERT, UPDATE, DELETE 
AS
BEGIN
IF @@ROWCOUNT = 0 OR
    TRIGGER_NESTLEVEL
    (
        OBJECT_ID(N'dbo.TransactionChange', N'TR'),
        'AFTER', 
        'DML'
    ) > 1 
    RETURN;

    SET NOCOUNT, XACT_ABORT ON;

    CREATE TABLE #Delta
    (
        AccountID integer PRIMARY KEY,
        Amount money NOT NULL
    );

    INSERT #Delta
        (AccountID, Amount)
    SELECT 
        InsDel.AccountID,
        Amount = SUM(InsDel.Amount)
    FROM 
    (
        SELECT AccountID, Amount
        FROM Inserted
        UNION ALL
        SELECT AccountID, $0 - Amount
        FROM Deleted
    ) AS InsDel
    GROUP BY
        InsDel.AccountID;

    UPDATE A
    SET Balance += D.Amount
    FROM #Delta AS D
    JOIN dbo.Accounts AS A WITH (SERIALIZABLE)
        ON A.AccountID = D.AccountID
    OPTION (RECOMPILE);
END;

Testing

The following code uses a table of numbers to create 100,000 accounts with a zero balance:

INSERT dbo.Accounts
    (AccountID, Balance)
SELECT
    N.n, $0
FROM dbo.Numbers AS N
WHERE
    N.n BETWEEN 1 AND 100000;

The test code below inserts 10,000 random transactions:

INSERT dbo.Transactions
    (AccountID, Amount)
SELECT 
    CONVERT(integer, RAND(CHECKSUM(NEWID())) * 100000 + 1),
    CONVERT(money, RAND(CHECKSUM(NEWID())) * 500 - 250)
FROM dbo.Numbers AS N
WHERE 
    N.n BETWEEN 1 AND 10000;

Using the SQLQueryStress tool, I ran this test 100 times on 32 threads with good performance, no deadlocks, and correct results. I still do not recommend this as anything other than a learning exercise.