create trigger [dbo].[TR_EVENTEXPENSE_INSERTUPDATE_CURRENCY] on [dbo].[EVENTEXPENSE]
after insert, update
not for replication
as
begin
set nocount on;
-- 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(BUDGETEDAMOUNT) or update(ACTUALAMOUNT) or update(AMOUNTPAID)
begin
declare @BASECURRENCYID uniqueidentifier;
set @BASECURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
update dbo.EVENTEXPENSE
set EVENTEXPENSE.ORGANIZATIONBUDGETEDAMOUNT = EVENTEXPENSE.BUDGETEDAMOUNT,
EVENTEXPENSE.ORGANIZATIONACTUALAMOUNT = EVENTEXPENSE.ACTUALAMOUNT,
EVENTEXPENSE.ORGANIZATIONAMOUNTPAID = EVENTEXPENSE.AMOUNTPAID,
EVENTEXPENSE.BASECURRENCYID = @BASECURRENCYID,
EVENTEXPENSE.CHANGEDBYID = EVENTEXPENSE.CHANGEDBYID,
EVENTEXPENSE.DATECHANGED = EVENTEXPENSE.DATECHANGED
from inserted
inner join dbo.EVENTEXPENSE on inserted.ID = EVENTEXPENSE.ID
where EVENTEXPENSE.ORGANIZATIONEXCHANGERATEID is null
and (EVENTEXPENSE.BASECURRENCYID is null or EVENTEXPENSE.BASECURRENCYID = @BASECURRENCYID)
and
(
EVENTEXPENSE.ORGANIZATIONBUDGETEDAMOUNT <> EVENTEXPENSE.BUDGETEDAMOUNT
or EVENTEXPENSE.ORGANIZATIONACTUALAMOUNT <> EVENTEXPENSE.ACTUALAMOUNT
or EVENTEXPENSE.ORGANIZATIONAMOUNTPAID <> EVENTEXPENSE.AMOUNTPAID
or (EVENTEXPENSE.BUDGETEDAMOUNT = 0
and EVENTEXPENSE.ACTUALAMOUNT = 0
and EVENTEXPENSE.AMOUNTPAID = 0)
);
end
end
|
Trigger Definition
