Thank you for your help, here is my script:
Stored Procedure:
ALTER PROC [dbo].[usp_UpdateAccountBalance] @AccountId INT, @Amount MONEY
AS
DECLARE @Count INT;
SELECT @Count = COUNT(*) FROM dbo.AccountBalance
WHERE AccountId = @AccountId;
IF (@Count = 0)
BEGIN
INSERT INTO dbo.AccountBalance
( AccountId ,
Balance
)
VALUES ( @AccountId ,
@Amount -- Balance - money
)
END
ELSE
BEGIN
UPDATE dbo.AccountBalance
SET Balance = Balance + @Amount
WHERE AccountId = @AccountId;
END
Trigger on table JournalEntryLine table:
ALTER TRIGGER [dbo].[tr_AccountBalance]
ON [dbo].[JournalEntryLine]
AFTER INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON;
DECLARE @Amount MONEY;
DECLARE @AccountId INT;
DECLARE @action CHAR(8)
IF COLUMNS_UPDATED() <> 0 -- delete or update?
BEGIN
IF EXISTS (SELECT * FROM deleted) -- updated cols + old rows means action=update
SET @action = 'UPDATE'
ELSE
SET @action = 'INSERT' -- updated columns and nothing deleted means action=insert
END
ELSE -- delete
BEGIN
SET @action = 'DELETE'
END
------------------------------------------------------------------------
DECLARE @Debit MONEY, @Credit MONEY;
IF (@action = 'INSERT')
BEGIN
SELECT @AccountId = AccountId FROM INSERTED;
SELECT @Debit = Debit FROM INSERTED;
SELECT @Credit = Credit FROM INSERTED;
IF (@Debit > 0)
BEGIN
SET @Amount = @Debit;
END
ELSE
BEGIN
SET @Amount = -@Credit;
END
END
ELSE IF (@action = 'DELETE')
BEGIN
SELECT @AccountId = AccountId FROM DELETED;
SELECT @Debit = Debit FROM DELETED;
SELECT @Credit = Credit FROM DELETED;
IF (@Debit > 0)
BEGIN
SET @Amount = -@Debit;
END
ELSE
BEGIN
SET @Amount = -@Credit;
END
END
ELSE IF (@action = 'UPDATE')
BEGIN
SELECT @AccountId = AccountId FROM INSERTED;
SELECT @Debit = Debit FROM INSERTED;
SELECT @Credit = Credit FROM INSERTED;
DECLARE @DebitDeleted MONEY, @CreditDeleted MONEY;
SELECT @DebitDeleted = Debit FROM DELETED;
SELECT @CreditDeleted = Credit FROM DELETED;
SET @Debit = @Debit - @DebitDeleted;
SET @Credit = @Credit - @CreditDeleted;
SET @Amount = @Debit - @Credit;
END
EXEC usp_UpdateAccountBalance @AccountId, @Amount;