Synchronization using triggers

11 votes

1 answer

6199 views

sql-server trigger transaction data-synchronization acid

                          I have a requirement similar to previous discussions at:

 - https://dba.stackexchange.com/questions/5608/writing-a-simple-bank-schema-how-should-i-keep-my-balances-in-sync-with-their-t 
 - https://dba.stackexchange.com/questions/35025/trigger-in-combination-with-transaction 

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?
                        

Asked by Yiping (227 rep)

Apr 22, 2014, 02:08 AM
Last activity: May 8, 2020, 06:29 AM

Synchronization using triggers

Related Questions