Sql-server – Synchronization using triggers

aciddata synchronizationsql servertransactiontrigger

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?

Best Answer

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.

Related Question