Trigger to update balance after a transaction amount record has been changed-CodePudding

Tables account and transactions

Account = {accNumber, balance, ...}
Transaction = {accNumber, amount, transNumber, ...}

Where Transaction.accNumber references Account.accNumber

I have managed to create trigger to update balance after INSERT

CREATE TRIGGER TR_Account_Balance
ON Transactions AFTER INSERT
AS
BEGIN
    UPDATE account SET
        balance = ins.newBalance
    FROM (
        SELECT a.accnumber,a.balance   SUM(i.amount) AS newBalance
        FROM Account a
        INNER JOIN inserted i ON i.accNumber = a.accNumber
        GROUP BY a.accNumber, a.balance
    ) AS ins
    WHERE account.accnumber = ins.accnumber
END

Now I need to create a trigger that would change balance accordingly to transaction AFTER UPDATE.

Example:

   |accNumber | balance | ...
   |-----------------------------
   |        1 |   100   | ...

   |accNumber | amount | ...
   |-----------------------------
   |        1 |   20   | ...

If we UPDATE amount to 10, balance should change to 90. How can I possibly do that?

CodePudding user response：

You should really do this in a normalized fashion, by using a view. For better performance, you can index it.

Indexed views are subject to some restrictions, in particular:

No outer joins or applys
Must be schema-bound
Grouped views need COUNT_BIG and can only use SUM as another aggregate

CREATE VIEW dbo.vAccountBalance
WITH SCHEMABINDING AS

SELECT
  tr.accnumber,
  SUM(tr.amount) AS balance,
  COUNT_BIG(*) AS numTransactions -- this MUST be added
FROM dbo.Transactions tr;  -- schema-qualify

GO

CREATE UNIQUE CLUSTERED INDEX CX_vAccountBalance ON dbo.vAccountBalance (accnumber);

The server will maintain this index together with other indexes, during any insert, update or delete.

If you really wanted to do this in a trigger, you can use the following

Note how the Account table is only referenced once, and the difference is added, rather than self-joining again
Note how inserted and deleted are joined together by primary key, and the difference is summed

CREATE TRIGGER TR_Account_Balance
ON Transactions AFTER INSERT, UPDATE, DELETE
AS

SET NOCOUNT ON;

IF NOT EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
    RETURN;  -- early bail-out

UPDATE a  -- update the already referenced Account table
SET
    balance  = ins.diffBalance
FROM Account a
INNER JOIN (
    SELECT
      i.accnumber,
      SUM(i.amount) AS diffBalance
    FROM (
        SELECT i.transNumber, i.accnumber, i.amount
        FROM inserted i
    )
    FULL JOIN (
        SELECT d.transNumber, d.accnumber, -(d.amount)
        FROM deleted d
    ) ON i.transNumber = a.transNumber
    GROUP BY i.accNumber
) AS ins ON a.accnumber = ins.accnumber;

GO

You could also split this up into separate INSERT UPDATE and DELETE triggers, in which case you can remove the deleted section for the former, remove the inserted section for the latter, and change the UPDATE one to use an INNER JOIN instead of a FULL JOIN.

CodePudding user response：

If you do the insert update delete via a proc that will be the best place to update the mapped table or other tables as well. If you still want to do it in a trigger (Carefully) please compute your SUM at same table level and update the balance on main table so it'll cover update and delete as well.

Schema:

DROP TABLE IF EXISTS dbo.AccountTransaction
DROP TABLE IF EXISTS dbo.Account

CREATE TABLE dbo.Account
(
    AccountNumber INT            CONSTRAINT PK_AccountId PRIMARY KEY CLUSTERED IDENTITY(1, 1) NOT NULL,
    Balance       DECIMAL(18, 9) CONSTRAINT DF_Account_Balance DEFAULT 0.0 NOT NULL
)
GO

INSERT INTO dbo.Account
(
    Balance
)
VALUES
(
    DEFAULT -- decimal(18, 9)
)

CREATE TABLE dbo.AccountTransaction
(
    AccountTransactionId INT            CONSTRAINT PK_AccountTransactionId PRIMARY KEY CLUSTERED IDENTITY(1, 1) NOT NULL,
    AccountNumber        INT            CONSTRAINT FK_AccountTransaction_Account FOREIGN KEY REFERENCES dbo.Account (AccountNumber) NOT NULL,
    Amount               DECIMAL(18, 9) CONSTRAINT DF_AccountTransaction_Amount DEFAULT 0.0 NOT NULL
)
GO

CREATE TRIGGER dbo.tr_AccountTransaction
ON dbo.AccountTransaction
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Inserted AS INT =
            (
                SELECT COUNT (1)
                FROM   INSERTED
            )
    DECLARE @Deleted AS INT =
            (
                SELECT COUNT (1)
                FROM   DELETED
            )

    IF @Inserted > 0
    BEGIN
        UPDATE dbo.Account
        SET    Balance = x.NewBalance
        FROM
               (
                   SELECT   SUM (at.Amount) AS NewBalance
                   FROM     Inserted AS i
                   JOIN     dbo.AccountTransaction AS at
                       ON   at.AccountNumber = i.AccountNumber
                   GROUP BY i.AccountNumber
               ) AS x
    END

    IF @Inserted = 0
       AND @Deleted > 0
    BEGIN
        UPDATE dbo.Account
        SET    Balance = x.NewBalance
        FROM
               (
                   SELECT   SUM (at.Amount) AS NewBalance
                   FROM     Deleted AS d
                   JOIN     dbo.AccountTransaction AS at
                       ON   at.AccountNumber = d.AccountNumber
                   GROUP BY d.AccountNumber
               ) AS x
    END
END
GO

** DEBUG**

INSERT INTO dbo.AccountTransaction
(
    AccountNumber,
    Amount
)
SELECT a.AccountNumber,
       12.0
FROM   dbo.Account AS a

SELECT a.AccountNumber,
       a.Balance
FROM   dbo.Account AS a

UPDATE at
SET    at.Amount  = 30
FROM   dbo.AccountTransaction AS at
WHERE  at.AccountTransactionId = 1

SELECT a.AccountNumber,
       a.Balance
FROM   dbo.Account AS a
 
SELECT at.AccountTransactionId,
       at.AccountNumber,
       at.Amount
FROM   dbo.AccountTransaction AS at

UPDATE at
SET    at.Amount -= 20
FROM   dbo.AccountTransaction AS at
WHERE  at.AccountTransactionId = 1

SELECT a.AccountNumber,
       a.Balance
FROM   dbo.Account AS a
 
SELECT at.AccountTransactionId,
       at.AccountNumber,
       at.Amount
FROM   dbo.AccountTransaction AS at
DELETE a
FROM   dbo.AccountTransaction AS a
WHERE  a.AccountTransactionId = 2

SELECT a.AccountNumber,
       a.Balance
FROM   dbo.Account AS a

SELECT at.AccountTransactionId,
       at.AccountNumber,
       at.Amount
FROM   dbo.AccountTransaction AS at