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