CREATE trigger [dbo].[TR_REGISTRANTBENEFIT_INSERTUPDATE_CURRENCY] on [dbo].[REGISTRANTBENEFIT]
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(TOTALVALUE)
update
dbo.REGISTRANTBENEFIT
set
REGISTRANTBENEFIT.ORGANIZATIONTOTALVALUE = REGISTRANTBENEFIT.TOTALVALUE,
REGISTRANTBENEFIT.EVENTBASECURRENCYTOTALVALUE = REGISTRANTBENEFIT.TOTALVALUE,
REGISTRANTBENEFIT.BASECURRENCYID = @ORGANIZATIONCURRENCYID,
REGISTRANTBENEFIT.CHANGEDBYID = REGISTRANTBENEFIT.CHANGEDBYID,
REGISTRANTBENEFIT.DATECHANGED = REGISTRANTBENEFIT.DATECHANGED
from
inserted
inner join
dbo.REGISTRANTBENEFIT on inserted.ID = REGISTRANTBENEFIT.ID
inner join dbo.REGISTRANT on REGISTRANTBENEFIT.REGISTRANTID = REGISTRANT.ID
inner join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
where
REGISTRANTBENEFIT.ORGANIZATIONEXCHANGERATEID is null
and (REGISTRANTBENEFIT.BASECURRENCYID is null or REGISTRANTBENEFIT.BASECURRENCYID = @ORGANIZATIONCURRENCYID)
and REGISTRANTBENEFIT.BASEEXCHANGERATEID is null
and (EVENT.BASECURRENCYID is null or EVENT.BASECURRENCYID = @ORGANIZATIONCURRENCYID)
and
(
REGISTRANTBENEFIT.ORGANIZATIONTOTALVALUE <> REGISTRANTBENEFIT.TOTALVALUE
or REGISTRANTBENEFIT.EVENTBASECURRENCYTOTALVALUE <> REGISTRANTBENEFIT.TOTALVALUE
or REGISTRANTBENEFIT.TOTALVALUE = 0
)
end
|
Trigger Definition
