Trigger error and comments on it ?

Question

Trigger error and comments on it ?

slava_th

SSC Rookie

Points: 28
More actions
October 14, 2017 at 1:03 pm

#339894

Hello,
I have multiple triggers which do similar things, like updating some summary field of one table based on values/number of records in another table. They all have similar design. I don't do triggers each day since I have to do web forms/c# too. So I'd like to ask you where is the design wrong / ok. This triggger is to keep tbl_GLAccount.Balance (money) field in sync with sum(AmountDebit-AmountCredit) by tbl_GLTransaction for Account. There is an error somewhere: A couple of times during a day Balance field of an account does not match sum of it's transactions.
Here is the trigger, I added debug info to it, since it fails time to time. Error apperars to happen when a SP opens (sql) transaction and does multiple insert/update (Status = 'D' e.q. deleted) in it's loop on tbl_GLTransaction and finally Balance gets out of sync with one transaction Sum not added to Balance.
It calculates difference on Insert/Delete or re-counts Balance on Update and then updates tbl_GLTransaction. I'd already removed "with (nolock)", hope it might fix problem. Thank you
CREATE TRIGGER [dbo].[trg_AccountBalance]
ON [dbo].[tbl_GLTransaction]
AFTER INSERT, UPDATE, DELETE
AS
set nocount on
begin try
    declare @TriggerMessage varchar(max), @TriggerId int, @CurrentDateTime datetime = getutcdate()
    select @TriggerId = isnull(max(TriggerId), 0) + 1
    from uManageDBLogs.dbo.tbl_TriggerLog
declare @dbcc_INPUTBUFFER table(EventType nvarchar(30), Parameters Int, EventInfo nvarchar(4000) )
declare @my_spid varchar(20)
set @my_spid = CAST(@@SPID as varchar(20))
insert @dbcc_INPUTBUFFER
exec('DBCC INPUTBUFFER ('+@my_spid+')')
select @TriggerMessage = replace(EventInfo, '''', '''''') from @dbcc_INPUTBUFFER
    insert into uManageDBLogs.dbo.tbl_TriggerLog (TriggerId, "Message", CreateDate)
    values (@TriggerId, @TriggerMessage, @CurrentDateTime)
    declare @Oper int
    select @Oper = 0
    -- determine type of sql statement
    if exists (select * from inserted) select @Oper = @Oper + 1
    if exists (select * from deleted) select @Oper = @Oper + 2
    select @TriggerMessage = '@Oper = ' + convert(varchar, @Oper)
    insert into uManageDBLogs.dbo.tbl_TriggerLog (TriggerId, "Message", CreateDate)
    values (@TriggerId, @TriggerMessage, @CurrentDateTime)
    if @Oper = 0 return -- No data changed
    declare @TomorrowDate date = dateadd(day, 1, convert(date, getdate()))
    declare @CurrentDate date = convert(date, getdate())
    -- transactions from both inserted and deleted tables
    declare @tbl_Trans table (FirmId int, GLAccountId int,
        AmountDebit money, AmountCredit money, "Status" char(1), TableType char(1))
    declare @tbl_AccountCounters table (FirmId int, GLAccountId int, Balance money)
    declare @IsChange bit = null
    insert into @tbl_Trans (FirmId, GLAccountId, AmountDebit, AmountCredit, "Status", TableType)
        select FirmId, GLAccountId, AmountDebit, AmountCredit, "Status", 'I'
        from inserted
        union
        select FirmId, GLAccountId, AmountDebit, AmountCredit, "Status", 'D'
        from deleted
        select @TriggerMessage = (select * from @tbl_Trans for xml path ('tbl_Trans'))
        insert into uManageDBLogs.dbo.tbl_TriggerLog (TriggerId, "Message", CreateDate)
        values (@TriggerId, @TriggerMessage, @CurrentDateTime)
        insert into @tbl_AccountCounters (FirmId, GLAccountId, Balance)
            select FirmId, GLAccountId, 0
            from @tbl_Trans
            group by FirmId, GLAccountId
        if @Oper = 1 or @Oper = 2 -- insert/delete
        begin
            update @tbl_AccountCounters
            set Balance = cnt.TransSum
            from @tbl_AccountCounters as ac join
            (
                select trans.FirmId, trans.GLAccountId,
                    isnull(sum((trans.AmountDebit - trans.AmountCredit) * iif(trans.TableType = 'I', 1, -1)), 0) as TransSum
                from @tbl_Trans as trans
                where trans.Status = 'A'
                group by trans.FirmId, trans.GLAccountId
            ) as cnt on    ac.FirmId = cnt.FirmId and ac.GLAccountId = cnt.GLAccountId
            select @IsChange = 1
        end
        else
        begin
            if update(AmountDebit) or update(AmountCredit) or update(Status) or update(GLAccountId)
            begin
                update @tbl_AccountCounters
                set Balance = cnt.TransBalance
                from @tbl_AccountCounters as ac join
                    (select trans.FirmId, trans.GLAccountId, isnull(sum(trans.AmountDebit - trans.AmountCredit), 0) as TransBalance
                    from dbo.tbl_GLTransaction as trans with (nolock)
                    where trans."Status" = 'A' and exists (select 1 from @tbl_AccountCounters as ac
                        where ac.GLAccountId = trans.GLAccountId and ac.FirmId = trans.FirmId)
                    group by trans.FirmId, trans.GLAccountId) as cnt on
                        ac.FirmId = cnt.FirmId and ac.GLAccountId = cnt.GLAccountId
                select @IsChange = 0
            end
        end
        select @TriggerMessage = '@IsChange = ' + isnull(convert(varchar, @IsChange), 'null')
        insert into uManageDBLogs.dbo.tbl_TriggerLog (TriggerId, "Message", CreateDate)
        values (@TriggerId, @TriggerMessage, @CurrentDateTime)
        select @TriggerMessage = (select * from @tbl_AccountCounters for xml path ('tbl_AccountCounters'))
        insert into uManageDBLogs.dbo.tbl_TriggerLog (TriggerId, "Message", CreateDate)
        values (@TriggerId, @TriggerMessage, @CurrentDateTime)
        if @IsChange is not null
        begin
            update tbl_GLAccount
            set tbl_GLAccount.Balance = iif(@IsChange = 1, cnt.Balance + acc.Balance, cnt.Balance),
                tbl_GLAccount.LastUpdate = getutcdate(),
                tbl_GLAccount.LastUpdatedBy = 1
            from @tbl_AccountCounters as cnt join dbo.tbl_GLAccount as acc with (nolock) on
                cnt.FirmId = acc.FirmId and cnt.GLAccountId = acc.GLAccountId
            where cnt.Balance <> isnull(acc.Balance, 0)
        end
    end try
    begin catch
--        if XACT_STATE() = -1 rollback tran
        declare @ErrorLine varchar(max)
        select @ErrorLine = uManageDb.dbo.udf_GetErrorInfo()
        insert into uManageDb.dbo.tbl_TriggerError ("Name", "Message", CreateDate)
        values ('AccountingDB..trg_AccountBalance', @ErrorLine, GETUTCDATE())
    end catch

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TheCTEGuy Ten Centuries Points: 1032 More actions · Answer 1

slava_th - Saturday, October 14, 2017 1:03 PM
Hello,
I have multiple triggers which do similar things, like updating some summary field of one table based on values/number of records in another table. They all have similar design. I don't do triggers each day since I have to do web forms/c# too. So I'd like to ask you where is the design wrong / ok. This triggger is to keep tbl_GLAccount.Balance (money) field in sync with sum(AmountDebit-AmountCredit) by tbl_GLTransaction for Account. There is an error somewhere: A couple of times during a day Balance field of an account does not match sum of it's transactions.
Here is the trigger, I added debug info to it, since it fails time to time. Error apperars to happen when a SP opens (sql) transaction and does multiple insert/update (Status = 'D' e.q. deleted) in it's loop on tbl_GLTransaction and finally Balance gets out of sync with one transaction Sum not added to Balance.
It calculates difference on Insert/Delete or re-counts Balance on Update and then updates tbl_GLTransaction. I'd already removed "with (nolock)", hope it might fix problem. Thank you
CREATE TRIGGER [dbo].[trg_AccountBalance]
ON [dbo].[tbl_GLTransaction]
AFTER INSERT, UPDATE, DELETE
AS
set nocount on
begin try
    declare @TriggerMessage varchar(max), @TriggerId int, @CurrentDateTime datetime = getutcdate()
    select @TriggerId = isnull(max(TriggerId), 0) + 1
    from uManageDBLogs.dbo.tbl_TriggerLog
declare @dbcc_INPUTBUFFER table(EventType nvarchar(30), Parameters Int, EventInfo nvarchar(4000) )
declare @my_spid varchar(20)
set @my_spid = CAST(@@SPID as varchar(20))
insert @dbcc_INPUTBUFFER
exec('DBCC INPUTBUFFER ('+@my_spid+')')
select @TriggerMessage = replace(EventInfo, '''', '''''') from @dbcc_INPUTBUFFER
    insert into uManageDBLogs.dbo.tbl_TriggerLog (TriggerId, "Message", CreateDate)
    values (@TriggerId, @TriggerMessage, @CurrentDateTime)
    declare @Oper int
    select @Oper = 0
    -- determine type of sql statement
    if exists (select * from inserted) select @Oper = @Oper + 1
    if exists (select * from deleted) select @Oper = @Oper + 2
    select @TriggerMessage = '@Oper = ' + convert(varchar, @Oper)
    insert into uManageDBLogs.dbo.tbl_TriggerLog (TriggerId, "Message", CreateDate)
    values (@TriggerId, @TriggerMessage, @CurrentDateTime)
    if @Oper = 0 return -- No data changed
    declare @TomorrowDate date = dateadd(day, 1, convert(date, getdate()))
    declare @CurrentDate date = convert(date, getdate())
    -- transactions from both inserted and deleted tables
    declare @tbl_Trans table (FirmId int, GLAccountId int,
        AmountDebit money, AmountCredit money, "Status" char(1), TableType char(1))
    declare @tbl_AccountCounters table (FirmId int, GLAccountId int, Balance money)
    declare @IsChange bit = null
    insert into @tbl_Trans (FirmId, GLAccountId, AmountDebit, AmountCredit, "Status", TableType)
        select FirmId, GLAccountId, AmountDebit, AmountCredit, "Status", 'I'
        from inserted
        union
        select FirmId, GLAccountId, AmountDebit, AmountCredit, "Status", 'D'
        from deleted
        select @TriggerMessage = (select * from @tbl_Trans for xml path ('tbl_Trans'))
        insert into uManageDBLogs.dbo.tbl_TriggerLog (TriggerId, "Message", CreateDate)
        values (@TriggerId, @TriggerMessage, @CurrentDateTime)
        insert into @tbl_AccountCounters (FirmId, GLAccountId, Balance)
            select FirmId, GLAccountId, 0
            from @tbl_Trans
            group by FirmId, GLAccountId
        if @Oper = 1 or @Oper = 2 -- insert/delete
        begin
            update @tbl_AccountCounters
            set Balance = cnt.TransSum
            from @tbl_AccountCounters as ac join
            (
                select trans.FirmId, trans.GLAccountId,
                    isnull(sum((trans.AmountDebit - trans.AmountCredit) * iif(trans.TableType = 'I', 1, -1)), 0) as TransSum
                from @tbl_Trans as trans
                where trans.Status = 'A'
                group by trans.FirmId, trans.GLAccountId
            ) as cnt on    ac.FirmId = cnt.FirmId and ac.GLAccountId = cnt.GLAccountId
            select @IsChange = 1
        end
        else
        begin
            if update(AmountDebit) or update(AmountCredit) or update(Status) or update(GLAccountId)
            begin
                update @tbl_AccountCounters
                set Balance = cnt.TransBalance
                from @tbl_AccountCounters as ac join
                    (select trans.FirmId, trans.GLAccountId, isnull(sum(trans.AmountDebit - trans.AmountCredit), 0) as TransBalance
                    from dbo.tbl_GLTransaction as trans with (nolock)
                    where trans."Status" = 'A' and exists (select 1 from @tbl_AccountCounters as ac
                        where ac.GLAccountId = trans.GLAccountId and ac.FirmId = trans.FirmId)
                    group by trans.FirmId, trans.GLAccountId) as cnt on
                        ac.FirmId = cnt.FirmId and ac.GLAccountId = cnt.GLAccountId
                select @IsChange = 0
            end
        end
        select @TriggerMessage = '@IsChange = ' + isnull(convert(varchar, @IsChange), 'null')
        insert into uManageDBLogs.dbo.tbl_TriggerLog (TriggerId, "Message", CreateDate)
        values (@TriggerId, @TriggerMessage, @CurrentDateTime)
        select @TriggerMessage = (select * from @tbl_AccountCounters for xml path ('tbl_AccountCounters'))
        insert into uManageDBLogs.dbo.tbl_TriggerLog (TriggerId, "Message", CreateDate)
        values (@TriggerId, @TriggerMessage, @CurrentDateTime)
        if @IsChange is not null
        begin
            update tbl_GLAccount
            set tbl_GLAccount.Balance = iif(@IsChange = 1, cnt.Balance + acc.Balance, cnt.Balance),
                tbl_GLAccount.LastUpdate = getutcdate(),
                tbl_GLAccount.LastUpdatedBy = 1
            from @tbl_AccountCounters as cnt join dbo.tbl_GLAccount as acc with (nolock) on
                cnt.FirmId = acc.FirmId and cnt.GLAccountId = acc.GLAccountId
            where cnt.Balance <> isnull(acc.Balance, 0)
        end
    end try
    begin catch
--        if XACT_STATE() = -1 rollback tran
        declare @ErrorLine varchar(max)
        select @ErrorLine = uManageDb.dbo.udf_GetErrorInfo()
        insert into uManageDb.dbo.tbl_TriggerError ("Name", "Message", CreateDate)
        values ('AccountingDB..trg_AccountBalance', @ErrorLine, GETUTCDATE())
    end catch

What's the actual error msg..?

First solve the problem then write the code !

slava_th SSC Rookie Points: 28 More actions · Answer 2

TheCTEGuy - Monday, October 16, 2017 2:59 AM
slava_th - Saturday, October 14, 2017 1:03 PM
Hello,
I have multiple triggers which do similar things, like updating some summary field of one table based on values/number of records in another table. They all have similar design. I don't do triggers each day since I have to do web forms/c# too. So I'd like to ask you where is the design wrong / ok. This triggger is to keep tbl_GLAccount.Balance (money) field in sync with sum(AmountDebit-AmountCredit) by tbl_GLTransaction for Account. There is an error somewhere: A couple of times during a day Balance field of an account does not match sum of it's transactions.
Here is the trigger, I added debug info to it, since it fails time to time. Error apperars to happen when a SP opens (sql) transaction and does multiple insert/update (Status = 'D' e.q. deleted) in it's loop on tbl_GLTransaction and finally Balance gets out of sync with one transaction Sum not added to Balance.
It calculates difference on Insert/Delete or re-counts Balance on Update and then updates tbl_GLTransaction. I'd already removed "with (nolock)", hope it might fix problem. Thank you
CREATE TRIGGER [dbo].[trg_AccountBalance]
ON [dbo].[tbl_GLTransaction]
AFTER INSERT, UPDATE, DELETE
AS
set nocount on
begin try
    declare @TriggerMessage varchar(max), @TriggerId int, @CurrentDateTime datetime = getutcdate()
    select @TriggerId = isnull(max(TriggerId), 0) + 1
    from uManageDBLogs.dbo.tbl_TriggerLog
declare @dbcc_INPUTBUFFER table(EventType nvarchar(30), Parameters Int, EventInfo nvarchar(4000) )
declare @my_spid varchar(20)
set @my_spid = CAST(@@SPID as varchar(20))
insert @dbcc_INPUTBUFFER
exec('DBCC INPUTBUFFER ('+@my_spid+')')
select @TriggerMessage = replace(EventInfo, '''', '''''') from @dbcc_INPUTBUFFER
    insert into uManageDBLogs.dbo.tbl_TriggerLog (TriggerId, "Message", CreateDate)
    values (@TriggerId, @TriggerMessage, @CurrentDateTime)
    declare @Oper int
    select @Oper = 0
    -- determine type of sql statement
    if exists (select * from inserted) select @Oper = @Oper + 1
    if exists (select * from deleted) select @Oper = @Oper + 2
    select @TriggerMessage = '@Oper = ' + convert(varchar, @Oper)
    insert into uManageDBLogs.dbo.tbl_TriggerLog (TriggerId, "Message", CreateDate)
    values (@TriggerId, @TriggerMessage, @CurrentDateTime)
    if @Oper = 0 return -- No data changed
    declare @TomorrowDate date = dateadd(day, 1, convert(date, getdate()))
    declare @CurrentDate date = convert(date, getdate())
    -- transactions from both inserted and deleted tables
    declare @tbl_Trans table (FirmId int, GLAccountId int,
        AmountDebit money, AmountCredit money, "Status" char(1), TableType char(1))
    declare @tbl_AccountCounters table (FirmId int, GLAccountId int, Balance money)
    declare @IsChange bit = null
    insert into @tbl_Trans (FirmId, GLAccountId, AmountDebit, AmountCredit, "Status", TableType)
        select FirmId, GLAccountId, AmountDebit, AmountCredit, "Status", 'I'
        from inserted
        union
        select FirmId, GLAccountId, AmountDebit, AmountCredit, "Status", 'D'
        from deleted
        select @TriggerMessage = (select * from @tbl_Trans for xml path ('tbl_Trans'))
        insert into uManageDBLogs.dbo.tbl_TriggerLog (TriggerId, "Message", CreateDate)
        values (@TriggerId, @TriggerMessage, @CurrentDateTime)
        insert into @tbl_AccountCounters (FirmId, GLAccountId, Balance)
            select FirmId, GLAccountId, 0
            from @tbl_Trans
            group by FirmId, GLAccountId
        if @Oper = 1 or @Oper = 2 -- insert/delete
        begin
            update @tbl_AccountCounters
            set Balance = cnt.TransSum
            from @tbl_AccountCounters as ac join
            (
                select trans.FirmId, trans.GLAccountId,
                    isnull(sum((trans.AmountDebit - trans.AmountCredit) * iif(trans.TableType = 'I', 1, -1)), 0) as TransSum
                from @tbl_Trans as trans
                where trans.Status = 'A'
                group by trans.FirmId, trans.GLAccountId
            ) as cnt on    ac.FirmId = cnt.FirmId and ac.GLAccountId = cnt.GLAccountId
            select @IsChange = 1
        end
        else
        begin
            if update(AmountDebit) or update(AmountCredit) or update(Status) or update(GLAccountId)
            begin
                update @tbl_AccountCounters
                set Balance = cnt.TransBalance
                from @tbl_AccountCounters as ac join
                    (select trans.FirmId, trans.GLAccountId, isnull(sum(trans.AmountDebit - trans.AmountCredit), 0) as TransBalance
                    from dbo.tbl_GLTransaction as trans with (nolock)
                    where trans."Status" = 'A' and exists (select 1 from @tbl_AccountCounters as ac
                        where ac.GLAccountId = trans.GLAccountId and ac.FirmId = trans.FirmId)
                    group by trans.FirmId, trans.GLAccountId) as cnt on
                        ac.FirmId = cnt.FirmId and ac.GLAccountId = cnt.GLAccountId
                select @IsChange = 0
            end
        end
        select @TriggerMessage = '@IsChange = ' + isnull(convert(varchar, @IsChange), 'null')
        insert into uManageDBLogs.dbo.tbl_TriggerLog (TriggerId, "Message", CreateDate)
        values (@TriggerId, @TriggerMessage, @CurrentDateTime)
        select @TriggerMessage = (select * from @tbl_AccountCounters for xml path ('tbl_AccountCounters'))
        insert into uManageDBLogs.dbo.tbl_TriggerLog (TriggerId, "Message", CreateDate)
        values (@TriggerId, @TriggerMessage, @CurrentDateTime)
        if @IsChange is not null
        begin
            update tbl_GLAccount
            set tbl_GLAccount.Balance = iif(@IsChange = 1, cnt.Balance + acc.Balance, cnt.Balance),
                tbl_GLAccount.LastUpdate = getutcdate(),
                tbl_GLAccount.LastUpdatedBy = 1
            from @tbl_AccountCounters as cnt join dbo.tbl_GLAccount as acc with (nolock) on
                cnt.FirmId = acc.FirmId and cnt.GLAccountId = acc.GLAccountId
            where cnt.Balance <> isnull(acc.Balance, 0)
        end
    end try
    begin catch
--        if XACT_STATE() = -1 rollback tran
        declare @ErrorLine varchar(max)
        select @ErrorLine = uManageDb.dbo.udf_GetErrorInfo()
        insert into uManageDb.dbo.tbl_TriggerError ("Name", "Message", CreateDate)
        values ('AccountingDB..trg_AccountBalance', @ErrorLine, GETUTCDATE())
    end catch
What's the actual error msg..?

No error message, just sum of amounts in transaction table does not match Balance on an account table after a serie of trigger runs.

slava_th SSC Rookie Points: 28 More actions · Answer 3

slava_th - Monday, October 16, 2017 1:23 PM
TheCTEGuy - Monday, October 16, 2017 2:59 AM
slava_th - Saturday, October 14, 2017 1:03 PM
Hello,
I have multiple triggers which do similar things, like updating some summary field of one table based on values/number of records in another table. They all have similar design. I don't do triggers each day since I have to do web forms/c# too. So I'd like to ask you where is the design wrong / ok. This triggger is to keep tbl_GLAccount.Balance (money) field in sync with sum(AmountDebit-AmountCredit) by tbl_GLTransaction for Account. There is an error somewhere: A couple of times during a day Balance field of an account does not match sum of it's transactions.
Here is the trigger, I added debug info to it, since it fails time to time. Error apperars to happen when a SP opens (sql) transaction and does multiple insert/update (Status = 'D' e.q. deleted) in it's loop on tbl_GLTransaction and finally Balance gets out of sync with one transaction Sum not added to Balance.
It calculates difference on Insert/Delete or re-counts Balance on Update and then updates tbl_GLTransaction. I'd already removed "with (nolock)", hope it might fix problem. Thank you
CREATE TRIGGER [dbo].[trg_AccountBalance]
ON [dbo].[tbl_GLTransaction]
AFTER INSERT, UPDATE, DELETE
AS
set nocount on
begin try
    declare @TriggerMessage varchar(max), @TriggerId int, @CurrentDateTime datetime = getutcdate()
    select @TriggerId = isnull(max(TriggerId), 0) + 1
    from uManageDBLogs.dbo.tbl_TriggerLog
declare @dbcc_INPUTBUFFER table(EventType nvarchar(30), Parameters Int, EventInfo nvarchar(4000) )
declare @my_spid varchar(20)
set @my_spid = CAST(@@SPID as varchar(20))
insert @dbcc_INPUTBUFFER
exec('DBCC INPUTBUFFER ('+@my_spid+')')
select @TriggerMessage = replace(EventInfo, '''', '''''') from @dbcc_INPUTBUFFER
    insert into uManageDBLogs.dbo.tbl_TriggerLog (TriggerId, "Message", CreateDate)
    values (@TriggerId, @TriggerMessage, @CurrentDateTime)
    declare @Oper int
    select @Oper = 0
    -- determine type of sql statement
    if exists (select * from inserted) select @Oper = @Oper + 1
    if exists (select * from deleted) select @Oper = @Oper + 2
    select @TriggerMessage = '@Oper = ' + convert(varchar, @Oper)
    insert into uManageDBLogs.dbo.tbl_TriggerLog (TriggerId, "Message", CreateDate)
    values (@TriggerId, @TriggerMessage, @CurrentDateTime)
    if @Oper = 0 return -- No data changed
    declare @TomorrowDate date = dateadd(day, 1, convert(date, getdate()))
    declare @CurrentDate date = convert(date, getdate())
    -- transactions from both inserted and deleted tables
    declare @tbl_Trans table (FirmId int, GLAccountId int,
        AmountDebit money, AmountCredit money, "Status" char(1), TableType char(1))
    declare @tbl_AccountCounters table (FirmId int, GLAccountId int, Balance money)
    declare @IsChange bit = null
    insert into @tbl_Trans (FirmId, GLAccountId, AmountDebit, AmountCredit, "Status", TableType)
        select FirmId, GLAccountId, AmountDebit, AmountCredit, "Status", 'I'
        from inserted
        union
        select FirmId, GLAccountId, AmountDebit, AmountCredit, "Status", 'D'
        from deleted
        select @TriggerMessage = (select * from @tbl_Trans for xml path ('tbl_Trans'))
        insert into uManageDBLogs.dbo.tbl_TriggerLog (TriggerId, "Message", CreateDate)
        values (@TriggerId, @TriggerMessage, @CurrentDateTime)
        insert into @tbl_AccountCounters (FirmId, GLAccountId, Balance)
            select FirmId, GLAccountId, 0
            from @tbl_Trans
            group by FirmId, GLAccountId
        if @Oper = 1 or @Oper = 2 -- insert/delete
        begin
            update @tbl_AccountCounters
            set Balance = cnt.TransSum
            from @tbl_AccountCounters as ac join
            (
                select trans.FirmId, trans.GLAccountId,
                    isnull(sum((trans.AmountDebit - trans.AmountCredit) * iif(trans.TableType = 'I', 1, -1)), 0) as TransSum
                from @tbl_Trans as trans
                where trans.Status = 'A'
                group by trans.FirmId, trans.GLAccountId
            ) as cnt on    ac.FirmId = cnt.FirmId and ac.GLAccountId = cnt.GLAccountId
            select @IsChange = 1
        end
        else
        begin
            if update(AmountDebit) or update(AmountCredit) or update(Status) or update(GLAccountId)
            begin
                update @tbl_AccountCounters
                set Balance = cnt.TransBalance
                from @tbl_AccountCounters as ac join
                    (select trans.FirmId, trans.GLAccountId, isnull(sum(trans.AmountDebit - trans.AmountCredit), 0) as TransBalance
                    from dbo.tbl_GLTransaction as trans with (nolock)
                    where trans."Status" = 'A' and exists (select 1 from @tbl_AccountCounters as ac
                        where ac.GLAccountId = trans.GLAccountId and ac.FirmId = trans.FirmId)
                    group by trans.FirmId, trans.GLAccountId) as cnt on
                        ac.FirmId = cnt.FirmId and ac.GLAccountId = cnt.GLAccountId
                select @IsChange = 0
            end
        end
        select @TriggerMessage = '@IsChange = ' + isnull(convert(varchar, @IsChange), 'null')
        insert into uManageDBLogs.dbo.tbl_TriggerLog (TriggerId, "Message", CreateDate)
        values (@TriggerId, @TriggerMessage, @CurrentDateTime)
        select @TriggerMessage = (select * from @tbl_AccountCounters for xml path ('tbl_AccountCounters'))
        insert into uManageDBLogs.dbo.tbl_TriggerLog (TriggerId, "Message", CreateDate)
        values (@TriggerId, @TriggerMessage, @CurrentDateTime)
        if @IsChange is not null
        begin
            update tbl_GLAccount
            set tbl_GLAccount.Balance = iif(@IsChange = 1, cnt.Balance + acc.Balance, cnt.Balance),
                tbl_GLAccount.LastUpdate = getutcdate(),
                tbl_GLAccount.LastUpdatedBy = 1
            from @tbl_AccountCounters as cnt join dbo.tbl_GLAccount as acc with (nolock) on
                cnt.FirmId = acc.FirmId and cnt.GLAccountId = acc.GLAccountId
            where cnt.Balance <> isnull(acc.Balance, 0)
        end
    end try
    begin catch
--        if XACT_STATE() = -1 rollback tran
        declare @ErrorLine varchar(max)
        select @ErrorLine = uManageDb.dbo.udf_GetErrorInfo()
        insert into uManageDb.dbo.tbl_TriggerError ("Name", "Message", CreateDate)
        values ('AccountingDB..trg_AccountBalance', @ErrorLine, GETUTCDATE())
    end catch
What's the actual error msg..?
No error message, just sum of amounts in transaction table does not match Balance on an account table after a serie of trigger runs.

I think I'd found my bug.
It was in the last update's "where" clause.
--where cnt.Balance <> isnull(acc.Balance, 0)
it should have been where iif(@IsChange = 1, cnt.Balance + acc.Balance, cnt.Balance) <> isnull(acc.Balance, 0)
I've decided to remove "where" at all for simplicity 🙂

Phil Parkin SSC Guru Points: 246962 More actions · Answer 4

I think that you are going to have to do some more work on this yourself.
If you cannot provide some sort of detail or demonstration which describes the problems, how can you expect others to fix your code?
My only comments at this stage are

You should remove the (nolock) hints, unless you are happy to accept the risk of phantom data, missed data and data read more than once in your query results.
Getting the next value in a series by doing select max() + 1 is sometimes going to result in collisions. Better to use an IDENTITY or SEQUENCE.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin