Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a requirement similar to previous discussions at:

I have two tables, [Account].[Balance] and [Transaction].[Amount]:

CREATE TABLE Account (
      AccountID    INT
    , Balance      MONEY
);

CREATE TABLE Transaction (
      TransactionID INT
     , AccountID    INT
    , Amount      MONEY
);

When there is an insert, update or delete against the [Transaction] table, the [Account].[Balance] should be updated based on the [Amount].

Currently I have a trigger to do this job:

ALTER TRIGGER [dbo].[TransactionChanged] 
ON  [dbo].[Transaction]
AFTER INSERT, UPDATE, DELETE
AS 
BEGIN
IF  EXISTS (select 1 from [Deleted]) OR EXISTS (select 1 from [Inserted])
    UPDATE [dbo].[Account]
    SET
    [Account].[Balance] = [Account].[Balance] + 
        (
            Select ISNULL(Sum([Inserted].[Amount]),0)
            From [Inserted] 
            Where [Account].[AccountID] = [Inserted].[AccountID]
        )
        -
        (
            Select ISNULL(Sum([Deleted].[Amount]),0)
            From [Deleted] 
            Where [Account].[AccountID] = [Deleted].[AccountID]
        )
END

Although this seems to be working, I have questions:

  1. Does the trigger follow the relational database's ACID principle? Is there any chance an insert might be committed but the trigger fail?
  2. My IF and UPDATE statements look strange. Is there any better way to update the correct [Account] row?
share|improve this question
1  
#2 Why do you want to do it in SQL and not in an application? Also have you considered integrity checking as a scheduled task? (Go through each account and ensure each balance matches the transactions) –  Paul Apr 22 at 12:40
    
@Paul Yes I have my business logic layer doing the update. But I would like to know is it possible to do it just using trigger since it is a Database level feature (like you still set FK constrain for tables) –  Yiping Apr 22 at 13:36
1  
@Paul a scheduled task is not fast enough. Imagine my account balance is $100, I can take out $100, then if I try again before the scheduled task runs, I could take out $100 again. –  Aaron Bertrand Apr 22 at 13:45
1  
@Paul how exactly am I being snippy? I'm not going off on any tangents. The OP is asking about how to consolidate account balances when transactions take place, and you recommended using a scheduled task after the fact. I think for a bank it's very important to understand the problem with that recommendation. –  Aaron Bertrand Apr 22 at 14:07
2  
@Paul Oh, so you meant in addition to the trigger? It doesn't come across that way; it sounds like as an alternative to - please read it again. –  Aaron Bertrand Apr 22 at 14:11

1 Answer 1

up vote 9 down vote accepted

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.

share|improve this answer
    
Thanks for the detailed answer. Before digesting the whole thing, may I ask why it is "SELECT AccountID, $0 - Amount"? –  Yiping Apr 23 at 4:10
    
So I assume here it is equivalent to "SELECT AccountID, -Amount" ? Just wonder any particular for the "$0" in front –  Yiping Apr 23 at 8:50
    
@Yiping Yes, exactly the same. I used (0 - x) because unary minus can be easy to miss. $0 is just zero typed as money (the same type as the Amount column). –  Paul White Apr 23 at 10:18
    
If a good trigger is really this complicated even for a simple balance keeping logic, a trigger solution may not be a good idea. Because the same goal can be archived using business logic with 3~4 lines of code only. –  Yiping Apr 24 at 1:13
    
@Yiping I hope I made it clear in my answer that I do not favour trigger code for this sort of requirement. Indexed views and constraints have important benefits. I'm not sure exactly what you mean by "business logic" (stored procedure? application-level code?) but the same fundamental issues exist, and you have to be very careful. If correctness is not actually rigorously enforced in the database, things can go wrong. –  Paul White Apr 24 at 2:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.