TR_BANKACCOUNTDEPOSITCREDITPAYMENT_IUD

                    CREATE trigger [dbo].[TR_BANKACCOUNTDEPOSITCREDITPAYMENT_IUD] 
                        on [dbo].[BANKACCOUNTDEPOSITCREDITPAYMENT] 
                        for insert,update,delete 
                        not for replication 
                    as
                    begin
                        set nocount on                

                        --see if the new deposit is locked - if so, raise an error

                        if exists(
                            select INSERTED.DEPOSITID 
                            from INSERTED 
                            inner join dbo.[BANKACCOUNTDEPOSIT] 
                                on [BANKACCOUNTDEPOSIT].[ID] = INSERTED.DEPOSITID
                            where
                                INSERTED.DEPOSITID is not null and
                                [BANKACCOUNTDEPOSIT].[STATUSCODE] = 0
                        ) 
                        begin
                            raiserror('Refund payments cannot be added to locked deposits.',  16, 1)
                            rollback
                        end

                        --see if the new bank is closed - if so, raise an error

                        if exists(
                            select INSERTED.DEPOSITID 
                            from INSERTED 
                            inner join dbo.[BANKACCOUNTTRANSACTION]
                                on INSERTED.DEPOSITID = [BANKACCOUNTTRANSACTION].[ID]
                            inner join dbo.[BANKACCOUNT]
                                on [BANKACCOUNTTRANSACTION].[BANKACCOUNTID] = [BANKACCOUNT].[ID]
                            where [BANKACCOUNT].[STATUSCODE] = 0
                        ) 
                        begin
                            raiserror('Refund payments cannot be added to deposits associated with closed bank accounts.',  16, 1)
                            rollback
                        end

                        --see if the payment method is invalid for the new deposit - if so, raise an error

                        if exists(
                            select INSERTED.DEPOSITID 
                            from INSERTED 
                            inner join dbo.[BANKACCOUNTTRANSACTION]
                                on INSERTED.DEPOSITID = [BANKACCOUNTTRANSACTION].[ID]
                            where dbo.[UFN_BANKACCOUNTDEPOSIT_VALIDCREDITPAYMENTTYPE](INSERTED.DEPOSITID, INSERTED.ID) = 0
                        ) 
                        begin
                            raiserror('The selected refund method is not allowed for the deposit specified.',  16, 1) 
                            rollback
                        end

                        --*********************************************

                        --place all error conditions ABOVE this comment

                        --*********************************************


                        begin try
                            --update the new deposit's payment amount

                            update dbo.BANKACCOUNTDEPOSIT
                            set 
                                TOTALPAYMENTAMOUNT = DEPOSIT2.TOTALPAYMENTAMOUNT - T1.AMOUNT
                                ,CHANGEDBYID = DEPOSIT2.CHANGEDBYID 
                                ,DATECHANGED = DEPOSIT2.DATECHANGED
                            FROM (SELECT INSERTED.DEPOSITID, SUM(COALESCE(CREDITPAYMENT.AMOUNT,0)) AMOUNT
                                        FROM INSERTED 
                                        INNER JOIN dbo.BANKACCOUNTTRANSACTION DEPOSIT1 ON INSERTED.DEPOSITID=DEPOSIT1.ID 
                                        INNER JOIN CREDITPAYMENT ON INSERTED.ID=CREDITPAYMENT.ID
                                        GROUP BY INSERTED.DEPOSITID) T1 
                            INNER JOIN dbo.BANKACCOUNTDEPOSIT DEPOSIT2 ON T1.DEPOSITID =DEPOSIT2.ID

                            merge into dbo.BANKACCOUNTTRANSACTION
                            using (SELECT INSERTED.DEPOSITID, SUM(COALESCE(CREDITPAYMENT.AMOUNT,0)) AMOUNT
                                FROM INSERTED 
                                INNER JOIN dbo.BANKACCOUNTTRANSACTION DEPOSIT1 ON INSERTED.DEPOSITID=DEPOSIT1.ID 
                                INNER JOIN CREDITPAYMENT ON INSERTED.ID=CREDITPAYMENT.ID
                                GROUP BY INSERTED.DEPOSITID
                            ) T1 on T1.DEPOSITID = BANKACCOUNTTRANSACTION.ID
                            when matched then update set
                                TRANSACTIONAMOUNT = BANKACCOUNTTRANSACTION.TRANSACTIONAMOUNT - T1.AMOUNT
                                ,AMOUNT = BANKACCOUNTTRANSACTION.AMOUNT - T1.AMOUNT
                                ,ORGANIZATIONAMOUNT = BANKACCOUNTTRANSACTION.ORGANIZATIONAMOUNT - T1.AMOUNT
                                ,CHANGEDBYID = BANKACCOUNTTRANSACTION.CHANGEDBYID
                                ,DATECHANGED = BANKACCOUNTTRANSACTION.DATECHANGED;

                            --update the old deposit's payment amount

                            update dbo.BANKACCOUNTDEPOSIT
                            set 
                                TOTALPAYMENTAMOUNT = DEPOSIT2.TOTALPAYMENTAMOUNT + T1.AMOUNT
                                ,CHANGEDBYID = DEPOSIT2.CHANGEDBYID 
                                ,DATECHANGED = DEPOSIT2.DATECHANGED
                            FROM (SELECT DELETED.DEPOSITID, SUM(COALESCE(CREDITPAYMENT.AMOUNT,0)) AMOUNT
                                        FROM DELETED 
                                        INNER JOIN dbo.BANKACCOUNTTRANSACTION DEPOSIT1 ON DELETED.DEPOSITID=DEPOSIT1.ID 
                                        INNER JOIN CREDITPAYMENT ON DELETED.ID=CREDITPAYMENT.ID
                                        GROUP BY DELETED.DEPOSITID) T1 
                            INNER JOIN dbo.BANKACCOUNTDEPOSIT DEPOSIT2 ON T1.DEPOSITID =DEPOSIT2.ID

                            merge into dbo.BANKACCOUNTTRANSACTION
                            using (SELECT DELETED.DEPOSITID, SUM(COALESCE(CREDITPAYMENT.AMOUNT,0)) AMOUNT
                                FROM DELETED 
                                INNER JOIN dbo.BANKACCOUNTTRANSACTION DEPOSIT1 ON DELETED.DEPOSITID=DEPOSIT1.ID 
                                INNER JOIN CREDITPAYMENT ON DELETED.ID=CREDITPAYMENT.ID
                                GROUP BY DELETED.DEPOSITID
                            ) T1 on T1.DEPOSITID = BANKACCOUNTTRANSACTION.ID
                            when matched then update set
                                TRANSACTIONAMOUNT = BANKACCOUNTTRANSACTION.TRANSACTIONAMOUNT + T1.AMOUNT
                                ,AMOUNT = BANKACCOUNTTRANSACTION.AMOUNT + T1.AMOUNT
                                ,ORGANIZATIONAMOUNT = BANKACCOUNTTRANSACTION.ORGANIZATIONAMOUNT + T1.AMOUNT
                                ,CHANGEDBYID = BANKACCOUNTTRANSACTION.CHANGEDBYID
                                ,DATECHANGED = BANKACCOUNTTRANSACTION.DATECHANGED;

                        end try
                        begin catch
                            --Catch the arithmetic overflow error and re-raise a prettier one

                            if ERROR_NUMBER() = 8115 
                            begin
                                --NOTE: the following error message needs to be revisited

                                raiserror('The maximum supported deposit amount has been exceeded.',  16, 1)
                                rollback transaction
                            end 
                            else
                                exec dbo.USP_RAISE_ERROR
                        end catch
                    end