CREATE trigger [dbo].[TR_PLANNEDGIFT_INSERTUPDATE_CURRENCY] on [dbo].[PLANNEDGIFT]
after insert, update
not for replication
as
begin
set nocount on;
declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
-- if we try to save an amount without explicitly setting an organization amount and an exchange rate,
-- copy the amount to the organization amount.
if update(EXPECTEDGIFTAMOUNT) or update(RECOGNITIONAMOUNT) or update(NETPRESENTVALUE) or update(REMAINDERVALUE) or update(PAYOUTAMOUNT) or update(LIFEINSURANCEPREMIUM) or update(TOTALPAYOUT) or update(REALIZEDVALUE)
begin
update
dbo.PLANNEDGIFT
set
PLANNEDGIFT.ORGANIZATIONEXPECTEDGIFTAMOUNT = PLANNEDGIFT.EXPECTEDGIFTAMOUNT,
PLANNEDGIFT.TRANSACTIONEXPECTEDGIFTAMOUNT = PLANNEDGIFT.EXPECTEDGIFTAMOUNT,
PLANNEDGIFT.ORGANIZATIONRECOGNITIONAMOUNT = PLANNEDGIFT.RECOGNITIONAMOUNT,
PLANNEDGIFT.TRANSACTIONRECOGNITIONAMOUNT = PLANNEDGIFT.RECOGNITIONAMOUNT,
PLANNEDGIFT.ORGANIZATIONNETPRESENTVALUE = PLANNEDGIFT.NETPRESENTVALUE,
PLANNEDGIFT.TRANSACTIONNETPRESENTVALUE = PLANNEDGIFT.NETPRESENTVALUE,
PLANNEDGIFT.ORGANIZATIONREMAINDERVALUE = PLANNEDGIFT.REMAINDERVALUE,
PLANNEDGIFT.TRANSACTIONREMAINDERVALUE = PLANNEDGIFT.REMAINDERVALUE,
PLANNEDGIFT.ORGANIZATIONPAYOUTAMOUNT = PLANNEDGIFT.PAYOUTAMOUNT,
PLANNEDGIFT.TRANSACTIONPAYOUTAMOUNT = PLANNEDGIFT.PAYOUTAMOUNT,
PLANNEDGIFT.ORGANIZATIONLIFEINSURANCEPREMIUM = PLANNEDGIFT.LIFEINSURANCEPREMIUM,
PLANNEDGIFT.TRANSACTIONLIFEINSURANCEPREMIUM = PLANNEDGIFT.LIFEINSURANCEPREMIUM,
PLANNEDGIFT.ORGANIZATIONTOTALPAYOUT = PLANNEDGIFT.TOTALPAYOUT,
PLANNEDGIFT.TRANSACTIONTOTALPAYOUT = PLANNEDGIFT.TOTALPAYOUT,
PLANNEDGIFT.ORGANIZATIONREALIZEDVALUE = PLANNEDGIFT.REALIZEDVALUE,
PLANNEDGIFT.TRANSACTIONREALIZEDVALUE = PLANNEDGIFT.REALIZEDVALUE,
PLANNEDGIFT.BASECURRENCYID = @ORGANIZATIONCURRENCYID,
PLANNEDGIFT.TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID,
PLANNEDGIFT.CHANGEDBYID = PLANNEDGIFT.CHANGEDBYID,
PLANNEDGIFT.DATECHANGED = PLANNEDGIFT.DATECHANGED
from
inserted
inner join
dbo.PLANNEDGIFT on inserted.ID = PLANNEDGIFT.ID
where
PLANNEDGIFT.ORGANIZATIONEXCHANGERATEID is null
and (PLANNEDGIFT.BASECURRENCYID is null or PLANNEDGIFT.BASECURRENCYID = @ORGANIZATIONCURRENCYID)
and PLANNEDGIFT.BASEEXCHANGERATEID is null
and (PLANNEDGIFT.TRANSACTIONCURRENCYID is null or PLANNEDGIFT.TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID)
and
(
PLANNEDGIFT.ORGANIZATIONEXPECTEDGIFTAMOUNT <> PLANNEDGIFT.EXPECTEDGIFTAMOUNT
or PLANNEDGIFT.TRANSACTIONEXPECTEDGIFTAMOUNT <> PLANNEDGIFT.EXPECTEDGIFTAMOUNT
or PLANNEDGIFT.ORGANIZATIONRECOGNITIONAMOUNT <> PLANNEDGIFT.RECOGNITIONAMOUNT
or PLANNEDGIFT.TRANSACTIONRECOGNITIONAMOUNT <> PLANNEDGIFT.RECOGNITIONAMOUNT
or PLANNEDGIFT.ORGANIZATIONNETPRESENTVALUE <> PLANNEDGIFT.NETPRESENTVALUE
or PLANNEDGIFT.TRANSACTIONNETPRESENTVALUE <> PLANNEDGIFT.NETPRESENTVALUE
or PLANNEDGIFT.ORGANIZATIONREMAINDERVALUE <> PLANNEDGIFT.REMAINDERVALUE
or PLANNEDGIFT.TRANSACTIONREMAINDERVALUE <> PLANNEDGIFT.REMAINDERVALUE
or PLANNEDGIFT.ORGANIZATIONPAYOUTAMOUNT <> PLANNEDGIFT.PAYOUTAMOUNT
or PLANNEDGIFT.TRANSACTIONPAYOUTAMOUNT <> PLANNEDGIFT.PAYOUTAMOUNT
or PLANNEDGIFT.ORGANIZATIONLIFEINSURANCEPREMIUM <> PLANNEDGIFT.LIFEINSURANCEPREMIUM
or PLANNEDGIFT.TRANSACTIONLIFEINSURANCEPREMIUM <> PLANNEDGIFT.LIFEINSURANCEPREMIUM
or PLANNEDGIFT.ORGANIZATIONTOTALPAYOUT <> PLANNEDGIFT.TOTALPAYOUT
or PLANNEDGIFT.TRANSACTIONTOTALPAYOUT <> PLANNEDGIFT.TOTALPAYOUT
or PLANNEDGIFT.ORGANIZATIONREALIZEDVALUE <> PLANNEDGIFT.REALIZEDVALUE
or PLANNEDGIFT.TRANSACTIONREALIZEDVALUE <> PLANNEDGIFT.REALIZEDVALUE
or (
PLANNEDGIFT.EXPECTEDGIFTAMOUNT = 0
and PLANNEDGIFT.RECOGNITIONAMOUNT = 0
and PLANNEDGIFT.NETPRESENTVALUE = 0
and PLANNEDGIFT.REMAINDERVALUE = 0
and PLANNEDGIFT.PAYOUTAMOUNT = 0
and PLANNEDGIFT.LIFEINSURANCEPREMIUM = 0
and PLANNEDGIFT.TOTALPAYOUT = 0
and PLANNEDGIFT.REALIZEDVALUE = 0
)
);
end
end
|
Trigger Definition
