使用觸發器同步

我有一個類似於之前討論的要求：

• 編寫一個簡單的銀行模式：我應該如何讓我的餘額與他們的交易歷史保持同步？
• 結合交易觸發

我有兩張桌子，[Account].[Balance]並且[Transaction].[Amount]：

CREATE TABLE Account (
     AccountID    INT
   , Balance      MONEY
);

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

當對[Transaction]錶進行插入、更新或刪除時，[Account].[Balance]應根據[Amount].

目前我有一個觸發器來完成這項工作：

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

雖然這似乎有效，但我有問題：

1. 觸發器是否遵循關係數據庫的 ACID 原則？是否有可能送出插入但觸發器失敗？
2. 我的IF和UPDATE陳述看起來很奇怪。有沒有更好的方法來更新正確的[Account]行？

1、觸發器是否遵循關係數據庫的ACID原則？是否有可能送出插入但觸發器失敗？

您連結到的相關問題部分回答了這個問題。觸發程式碼在與觸發它的 DML 語句相同的事務上下文中執行，保留您提到的 ACID 原則的原子部分。觸發語句和触發程式碼作為一個單元成功或失敗。

ACID 屬性還保證整個事務（包括觸發程式碼）將使數據庫處於不違反任何顯式約束（一致）的狀態，並且任何可恢復的送出效果都將在數據庫崩潰（持久）中倖存下來。

除非周圍（可能是隱式或自動送出）事務在SERIALIZABLE隔離級別上執行，否則不會自動保證隔離屬性。其他並發數據庫活動可能會干擾觸發器程式碼的正確操作。例如，帳戶餘額可能會在您閱讀之後和更新之前被另一個會話更改 - 一個經典的競爭條件。

2. 我的 IF 和 UPDATE 語句看起來很奇怪。有沒有更好的方法來更新正確的$$ Account $$排？

您連結到的另一個問題沒有提供任何基於觸發器的解決方案，這是有充分理由的。旨在保持非規範化結構同步的觸發程式碼可能非常難以正確進行正確測試。即使是具有多年經驗的非常高級的 SQL Server 人員也很難做到這一點。

在保持良好性能的同時保持所有場景的正確性並避免死鎖等問題增加了額外的難度。您的觸發程式碼遠非強大，即使只修改了單個交易，它也會更新*每個帳戶的餘額。*基於觸發器的解決方案存在各種風險和挑戰，這使得該任務非常不適合該技術領域相對較新的人。

為了說明一些問題，我在下面展示了一些範常式式碼。這不是一個經過嚴格測試的解決方案（觸發器很難！），我不建議您將其用作學習練習以外的任何東西。對於一個真實的系統，非觸發解決方案有重要的好處，所以你應該仔細查看其他問題的答案，並完全避免觸發的想法。

樣品表

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)
);

預防TRUNCATE TABLE

觸發器不會被TRUNCATE TABLE. 以下空表的存在純粹是為了防止Transactions表被截斷（被外鍵引用防止表截斷）：

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)
);

觸發器定義

以下觸發程式碼確保只維護必要的帳戶條目，並SERIALIZABLE在那裡使用語義。作為一個理想的副作用，這也避免了在使用行版本控制隔離級別時可能導致的不正確結果。如果源語句不影響任何行，該程式碼還會避免執行觸發器程式碼。臨時表和RECOMPILE提示用於避免因基數估計不准確導致的觸發器執行計劃問題：

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;

測試

以下程式碼使用數字表創建 100,000 個餘額為零的帳戶：

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

下面的測試程式碼插入了 10,000 個隨機事務：

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;

使用SQLQueryStress工具，我在 32 個執行緒上執行了 100 次這個測試，性能良好，沒有死鎖，結果正確。我仍然不建議將此作為學習練習以外的任何東西。

引用自：https://dba.stackexchange.com/questions/63609