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
|
Trigger Definition
