TR_REVENUE_DELETE_DEPOSITLINK
Definition
Copy
CREATE trigger [dbo].[TR_REVENUE_DELETE_DEPOSITLINK] on [dbo].[REVENUE] after delete, update not for replication as begin
SET NOCOUNT ON
--We can't put a 'on delete cascade' constraint on the BANKACCOUNTDEPOSITPAYMENT table, so we need to manually clean up here
if exists(select 1 from DELETED D left outer join INSERTED I on I.ID = D.ID where I.ID is null)
or exists(select 1 from DELETED D inner join INSERTED I on I.ID = D.ID where I.TRANSACTIONAMOUNT != D.TRANSACTIONAMOUNT)
begin
--update the deposit's balance
UPDATE dbo.BANKACCOUNTDEPOSIT
SET
BANKACCOUNTDEPOSIT.TOTALPAYMENTAMOUNT=BANKACCOUNTDEPOSIT.TOTALPAYMENTAMOUNT + T1.AMOUNT
,CHANGEDBYID = BANKACCOUNTDEPOSIT.CHANGEDBYID
,DATECHANGED = BANKACCOUNTDEPOSIT.DATECHANGED
FROM dbo.BANKACCOUNTDEPOSIT
inner join (select BANKACCOUNTDEPOSITPAYMENT.DEPOSITID, SUM(isnull(INSERTED.TRANSACTIONAMOUNT, isnull(INSERTED.AMOUNT, 0)) - isnull(DELETED.TRANSACTIONAMOUNT, isnull(DELETED.AMOUNT, 0))) as AMOUNT
from DELETED
left outer join INSERTED on INSERTED.ID = DELETED.ID
INNER JOIN dbo.BANKACCOUNTDEPOSITPAYMENT ON DELETED.ID=BANKACCOUNTDEPOSITPAYMENT.ID
group by BANKACCOUNTDEPOSITPAYMENT.DEPOSITID) T1 on T1.DEPOSITID = BANKACCOUNTDEPOSIT.ID
declare @ORGAMOUNTORIGINCODE tinyint;
declare @ORGCURRENCYID uniqueidentifier;
select @ORGAMOUNTORIGINCODE = ORGANIZATIONAMOUNTORIGINCODE from dbo.MULTICURRENCYCONFIGURATION;
select @ORGCURRENCYID = ID from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1;
--update the bank account transaction's balance
update dbo.BANKACCOUNTTRANSACTION set
TRANSACTIONAMOUNT = BANKACCOUNTTRANSACTION.TRANSACTIONAMOUNT + T1.TRANSACTIONAMOUNT
,AMOUNT = BANKACCOUNTTRANSACTION.AMOUNT + T1.BASEAMOUNT
,ORGANIZATIONAMOUNT = BANKACCOUNTTRANSACTION.ORGANIZATIONAMOUNT +T1.ORGAMOUNT
,CHANGEDBYID = BANKACCOUNTTRANSACTION.CHANGEDBYID
,DATECHANGED = BANKACCOUNTTRANSACTION.DATECHANGED
from dbo.BANKACCOUNTTRANSACTION
inner join (
select
SUM(isnull(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end, 0)
- isnull(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then DELETED.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(DELETED.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end, 0)
) as [TRANSACTIONAMOUNT]
,SUM(isnull(
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end, 0)
- isnull(
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then DELETED.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(DELETED.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then DELETED.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(DELETED.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end, 0)
) as [BASEAMOUNT]
,SUM(isnull(
case when @ORGAMOUNTORIGINCODE = 1
then
case when BA.TRANSACTIONCURRENCYID = @ORGCURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.ORGANIZATIONEXCHANGERATEID)
end
else
case when BAT.BASECURRENCYID = @ORGCURRENCYID
then
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end
,BAT.ORGANIZATIONEXCHANGERATEID)
end
end, 0)
- isnull(
case when @ORGAMOUNTORIGINCODE = 1
then
case when BA.TRANSACTIONCURRENCYID = @ORGCURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then DELETED.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(DELETED.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then DELETED.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(DELETED.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.ORGANIZATIONEXCHANGERATEID)
end
else
case when BAT.BASECURRENCYID = @ORGCURRENCYID
then
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then DELETED.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(DELETED.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then DELETED.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(DELETED.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then DELETED.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(DELETED.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then DELETED.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(DELETED.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end
,BAT.ORGANIZATIONEXCHANGERATEID)
end
end, 0)) as [ORGAMOUNT]
,D.ID
from DELETED
left outer join INSERTED R on R.ID = DELETED.ID
inner join dbo.BANKACCOUNTDEPOSITPAYMENT DP ON DELETED.ID = DP.ID
inner join dbo.BANKACCOUNTDEPOSIT D on D.ID = DP.DEPOSITID
inner join dbo.BANKACCOUNTTRANSACTION BAT on BAT.ID = D.ID
inner join dbo.BANKACCOUNT BA on BA.ID = BAT.BANKACCOUNTID
group by D.ID
) T1 on BANKACCOUNTTRANSACTION.ID = T1.ID
DELETE FROM dbo.BANKACCOUNTDEPOSITPAYMENT where ID in (select D.ID from DELETED D left outer join INSERTED I on I.ID = D.ID where I.ID is null)
end
end