CREATE trigger TR_FINANCIALTRANSACTION_LINEITEM_TOTAL_IUD on dbo.FINANCIALTRANSACTIONLINEITEM for insert, update, delete not for replication as
begin
set nocount on;
declare @ORGORIGIN tinyint; -- 1 means use transaction amount, other means use base
set @ORGORIGIN=0;
if exists(
select FT.ID
from dbo.FINANCIALTRANSACTION FT
left outer join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FT.ID = FTLI.FINANCIALTRANSACTIONID and (FTLI.TYPECODE = 0) and (FTLI.DELETEDON IS NULL)
where FT.TYPECODE>100 and (FT.ID in (select FINANCIALTRANSACTIONID from INSERTED) or FT.ID in (select FINANCIALTRANSACTIONID from DELETED))
group by FT.ID, FT.AMOUNT
having FT.AMOUNT != SUM(isnull(FTLI.AMOUNT, 0))
)
raiserror('ERR_FINANCIALTRANSACTION_LINEITEM_TOTALAMOUNT', 13,1);
-- Verify that when no currency exchange has been specified, the amounts are the same.
if update(AMOUNT) or update(ORGAMOUNT) or update(BASEAMOUNT)
begin
select @ORGORIGIN = ORGANIZATIONAMOUNTORIGINCODE from dbo.MULTICURRENCYCONFIGURATION;
if exists(
select I.ID
from inserted as I
inner join dbo.FINANCIALTRANSACTION as FT on I.FINANCIALTRANSACTIONID = FT.ID
where
FT.TYPECODE>100 and
(
(FT.BASEEXCHANGERATEID is null
and I.AMOUNT<>I.BASEAMOUNT
)
or
(FT.ORGEXCHANGERATEID is null
and (
(@ORGORIGIN=1 and I.AMOUNT <> I.ORGAMOUNT)
or (@ORGORIGIN<>1 and I.BASEAMOUNT <> I.ORGAMOUNT)
)
)
)
)
raiserror('ERR_FINANCIALTRANSACTION_LINEITEM_AMOUNTMISMATCH',16,1);
-- Verify that the total for the BASEAMOUNT matches what it should be...
if update(BASEAMOUNT) and exists(
select FT.ID from
(
select FT.ID, FT.TRANSACTIONCURRENCYID, FT.BASEEXCHANGERATEID, FT.AMOUNT, SUM(FTLI.BASEAMOUNT) LIBASEAMOUNT
from dbo.FINANCIALTRANSACTION FT
left outer join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FT.ID = FTLI.FINANCIALTRANSACTIONID and (FTLI.TYPECODE = 0) and (FTLI.DELETEDON IS NULL)
where FT.TYPECODE>100 and (FT.ID in (select FINANCIALTRANSACTIONID from INSERTED) or FT.ID in (select FINANCIALTRANSACTIONID from DELETED))
group by FT.ID, FT.AMOUNT, FT.BASEEXCHANGERATEID, FT.TRANSACTIONCURRENCYID
) as FT
inner join dbo.CURRENCY as C on FT.TRANSACTIONCURRENCYID = C.ID
left outer join dbo.CURRENCYEXCHANGERATE as X on FT.BASEEXCHANGERATEID = X.ID
where
dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(FT.AMOUNT,coalesce(X.RATE,1)),C.DECIMALDIGITS,C.ROUNDINGTYPECODE) <> FT.LIBASEAMOUNT
)
raiserror('ERR_FINANCIALTRANSACTION_LINEITEM_TOTALBASEAMOUNT',16,1);
-- Verify that the total for ORGAMOUNT matches what it should be...
if update(ORGAMOUNT) and exists(
select FT.ID from
(
select FT.ID, FT.TRANSACTIONCURRENCYID, FT.ORGEXCHANGERATEID, FT.AMOUNT, SUM(FTLI.ORGAMOUNT) LIORGAMOUNT, SUM(FTLI.BASEAMOUNT) LIBASEAMOUNT
from dbo.FINANCIALTRANSACTION FT
left outer join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FT.ID = FTLI.FINANCIALTRANSACTIONID and (FTLI.TYPECODE = 0) and (FTLI.DELETEDON IS NULL)
where FT.TYPECODE>100 and (FT.ID in (select FINANCIALTRANSACTIONID from INSERTED) or FT.ID in (select FINANCIALTRANSACTIONID from DELETED))
group by FT.ID, FT.AMOUNT, FT.ORGEXCHANGERATEID, FT.TRANSACTIONCURRENCYID
) as FT
inner join dbo.CURRENCY as C on FT.TRANSACTIONCURRENCYID = C.ID
left outer join dbo.CURRENCYEXCHANGERATE as X on FT.ORGEXCHANGERATEID = X.ID
where
(@ORGORIGIN = 1 and dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(FT.AMOUNT,coalesce(X.RATE,1)),C.DECIMALDIGITS,C.ROUNDINGTYPECODE) <> FT.LIORGAMOUNT)
or
(@ORGORIGIN<> 1 and dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(FT.LIBASEAMOUNT,coalesce(X.RATE,1)),C.DECIMALDIGITS,C.ROUNDINGTYPECODE) <> FT.LIORGAMOUNT)
)
raiserror('ERR_FINANCIALTRANSACTION_LINEITEM_TOTALORGAMOUNT',16,1);
end;
end;
|
Trigger Definition
