create trigger [dbo].[TR_REVENUEVAT_INSERTUPDATE_CURRENCY] on [dbo].[REVENUEVAT]
after insert, update
not for replication
as
begin
set nocount on;
-- if we try to save a unit value without explicitly setting an organization total value and an exchange rate,
-- copy the total amount to the organization total amount and transaction total amount.
if update(VATAMOUNT) or update(AMOUNTTOTAX)
begin
declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
update
dbo.REVENUEVAT
set
REVENUEVAT.ORGANIZATIONVATAMOUNT = REVENUEVAT.VATAMOUNT,
REVENUEVAT.TRANSACTIONVATAMOUNT = REVENUEVAT.VATAMOUNT,
REVENUEVAT.ORGANIZATIONAMOUNTTOTAX = REVENUEVAT.AMOUNTTOTAX,
REVENUEVAT.TRANSACTIONAMOUNTTOTAX = REVENUEVAT.AMOUNTTOTAX,
REVENUEVAT.BASECURRENCYID = @ORGANIZATIONCURRENCYID,
REVENUEVAT.TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID,
REVENUEVAT.CHANGEDBYID = REVENUEVAT.CHANGEDBYID,
REVENUEVAT.DATECHANGED = REVENUEVAT.DATECHANGED
from
inserted
inner join
dbo.REVENUEVAT on inserted.ID = REVENUEVAT.ID
where
REVENUEVAT.ORGANIZATIONEXCHANGERATEID is null
and (REVENUEVAT.BASECURRENCYID is null or REVENUEVAT.BASECURRENCYID = @ORGANIZATIONCURRENCYID)
and REVENUEVAT.BASEEXCHANGERATEID is null
and (REVENUEVAT.TRANSACTIONCURRENCYID is null or REVENUEVAT.TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID)
and (
REVENUEVAT.ORGANIZATIONVATAMOUNT <> REVENUEVAT.VATAMOUNT
or REVENUEVAT.TRANSACTIONVATAMOUNT <> REVENUEVAT.VATAMOUNT
or REVENUEVAT.VATAMOUNT = 0 or
REVENUEVAT.ORGANIZATIONAMOUNTTOTAX <> REVENUEVAT.AMOUNTTOTAX
or REVENUEVAT.TRANSACTIONAMOUNTTOTAX <> REVENUEVAT.AMOUNTTOTAX
or REVENUEVAT.AMOUNTTOTAX = 0
);
end
end
|
Trigger Definition
