CREATE trigger [dbo].[TR_STOCKDETAIL_INSERTUPDATE_CURRENCY] on [dbo].[STOCKDETAIL]
after insert, update
not for replication
as
begin
set nocount on;
declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
-- If we try to save a sale amount or broker fee without explicitly setting an organization
-- amount and an exchange rate, copy the amount and fee to their organization and transaction
-- amount fields.
if update(MEDIANPRICE) or update(LOWPRICE) or update (HIGHPRICE)
begin
update
dbo.STOCKDETAIL
set
STOCKDETAIL.ORGANIZATIONMEDIANPRICE = STOCKDETAIL.MEDIANPRICE,
STOCKDETAIL.TRANSACTIONMEDIANPRICE = STOCKDETAIL.MEDIANPRICE,
STOCKDETAIL.ORGANIZATIONLOWPRICE = STOCKDETAIL.LOWPRICE,
STOCKDETAIL.TRANSACTIONLOWPRICE = STOCKDETAIL.LOWPRICE,
STOCKDETAIL.ORGANIZATIONHIGHPRICE = STOCKDETAIL.HIGHPRICE,
STOCKDETAIL.TRANSACTIONHIGHPRICE = STOCKDETAIL.HIGHPRICE,
STOCKDETAIL.BASECURRENCYID = @ORGANIZATIONCURRENCYID,
STOCKDETAIL.TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID,
STOCKDETAIL.CHANGEDBYID = STOCKDETAIL.CHANGEDBYID,
STOCKDETAIL.DATECHANGED = STOCKDETAIL.DATECHANGED
from
inserted
inner join
dbo.STOCKDETAIL on inserted.ID = STOCKDETAIL.ID
where
STOCKDETAIL.ORGANIZATIONEXCHANGERATEID is null
and (STOCKDETAIL.BASECURRENCYID is null or STOCKDETAIL.BASECURRENCYID = @ORGANIZATIONCURRENCYID)
and STOCKDETAIL.BASEEXCHANGERATEID is null
and (STOCKDETAIL.TRANSACTIONCURRENCYID is null or STOCKDETAIL.TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID)
and
(
STOCKDETAIL.ORGANIZATIONMEDIANPRICE <> STOCKDETAIL.MEDIANPRICE
or STOCKDETAIL.TRANSACTIONMEDIANPRICE <> STOCKDETAIL.MEDIANPRICE
or STOCKDETAIL.ORGANIZATIONLOWPRICE <> STOCKDETAIL.LOWPRICE
or STOCKDETAIL.TRANSACTIONLOWPRICE <> STOCKDETAIL.LOWPRICE
or STOCKDETAIL.ORGANIZATIONHIGHPRICE <> STOCKDETAIL.HIGHPRICE
or STOCKDETAIL.TRANSACTIONHIGHPRICE <> STOCKDETAIL.HIGHPRICE
or (
STOCKDETAIL.MEDIANPRICE = 0
and STOCKDETAIL.LOWPRICE = 0
and STOCKDETAIL.HIGHPRICE = 0
)
);
end
end
|
Trigger Definition
