CREATE trigger TR_CURRENCYEXCHANGERATE_INSERTUPDATEDELETE_DATEFIELDS on dbo.CURRENCYEXCHANGERATE
after insert, update, delete
not for replication
as begin
set nocount on;
--Update the ASOFDATE field if the TIMEZONEENTRYID is null
if update(ASOFDATE) or update(ASOFDATESDTZ)
begin
declare @SYSTEMDEFAULTTIMEZONEENTRYID uniqueidentifier = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULT();
update dbo.CURRENCYEXCHANGERATE set
CURRENCYEXCHANGERATE.TIMEZONEENTRYID = coalesce(inserted.TIMEZONEENTRYID, @SYSTEMDEFAULTTIMEZONEENTRYID),
CURRENCYEXCHANGERATE.ASOFDATE =
case
when inserted.TIMEZONEENTRYID is null
then dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(CURRENCYEXCHANGERATE.ASOFDATE, 0)
else
CURRENCYEXCHANGERATE.ASOFDATE
end,
CURRENCYEXCHANGERATE.ASOFDATESDTZ =
case
when inserted.TIMEZONEENTRYID is null
then CURRENCYEXCHANGERATE.ASOFDATE
else
dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(switchoffset(CURRENCYEXCHANGERATE.ASOFDATE, '+00:00'))
end,
CURRENCYEXCHANGERATE.CHANGEDBYID = CURRENCYEXCHANGERATE.CHANGEDBYID,
CURRENCYEXCHANGERATE.DATECHANGED = CURRENCYEXCHANGERATE.DATECHANGED
from
inserted
inner join dbo.CURRENCYEXCHANGERATE on inserted.ID = CURRENCYEXCHANGERATE.ID
where
inserted.TIMEZONEENTRYID is null
or CURRENCYEXCHANGERATE.ASOFDATESDTZ is null
or CURRENCYEXCHANGERATE.ASOFDATESDTZ <> dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(switchoffset(CURRENCYEXCHANGERATE.ASOFDATE, '+00:00'));
end
if (update(FROMCURRENCYID) or update(TOCURRENCYID) or update(ASOFDATE))
or -- Need the check below in order to update the appropriate rows for deletes.
(
not exists(select top 1 1 from inserted)
and exists(select top 1 1 from deleted)
)
begin
declare @DATESTOUPDATE table
(
ASOFDATE datetimeoffset,
FROMCURRENCYID uniqueidentifier,
TOCURRENCYID uniqueidentifier
);
insert into @DATESTOUPDATE(ASOFDATE,FROMCURRENCYID,TOCURRENCYID)
select distinct
DATA.ASOFDATE, DATA.FROMCURRENCYID, DATA.TOCURRENCYID
from
(
--Previous rates immediately before the updated rates based on the old ASOFDATE.
select
(
select top 1
PREVIOUSRATE.ASOFDATE
from
dbo.CURRENCYEXCHANGERATE as PREVIOUSRATE
where
PREVIOUSRATE.ASOFDATE < deleted.ASOFDATE
and deleted.FROMCURRENCYID = PREVIOUSRATE.FROMCURRENCYID
and deleted.TOCURRENCYID = PREVIOUSRATE.TOCURRENCYID
and PREVIOUSRATE.TYPECODE in (0,1)
and deleted.TYPECODE in (0,1)
order by
PREVIOUSRATE.ASOFDATE desc
) as ASOFDATE,
deleted.FROMCURRENCYID,
deleted.TOCURRENCYID
from
deleted
union all
--Rates having the same ASOFDATE as the old ASOFDATE of the updated rates.
select
CURRENCYEXCHANGERATE.ASOFDATE,
deleted.FROMCURRENCYID,
deleted.TOCURRENCYID
from
dbo.CURRENCYEXCHANGERATE
inner join deleted on
CURRENCYEXCHANGERATE.ASOFDATE = deleted.ASOFDATE
and CURRENCYEXCHANGERATE.FROMCURRENCYID = deleted.FROMCURRENCYID
and CURRENCYEXCHANGERATE.TOCURRENCYID = deleted.TOCURRENCYID
and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
and deleted.TYPECODE in (0,1)
union all
--Previous rates immediately before the updated rates based on the new ASOFDATE.
select
(
select top 1
PREVIOUSRATE.ASOFDATE
from
dbo.CURRENCYEXCHANGERATE as PREVIOUSRATE
where
PREVIOUSRATE.ASOFDATE < inserted.ASOFDATE
and inserted.FROMCURRENCYID = PREVIOUSRATE.FROMCURRENCYID
and inserted.TOCURRENCYID = PREVIOUSRATE.TOCURRENCYID
and PREVIOUSRATE.TYPECODE in (0,1)
and inserted.TYPECODE in (0,1)
order by
PREVIOUSRATE.ASOFDATE desc
) as ASOFDATE,
inserted.FROMCURRENCYID,
inserted.TOCURRENCYID
from
inserted
union all
--Rates having the same ASOFDATE as the new ASOFDATE of the updated rates.
select
CURRENCYEXCHANGERATE.ASOFDATE,
inserted.FROMCURRENCYID,
inserted.TOCURRENCYID
from
dbo.CURRENCYEXCHANGERATE
inner join inserted on
CURRENCYEXCHANGERATE.ASOFDATE = inserted.ASOFDATE
and CURRENCYEXCHANGERATE.FROMCURRENCYID = inserted.FROMCURRENCYID
and CURRENCYEXCHANGERATE.TOCURRENCYID = inserted.TOCURRENCYID
and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
and inserted.TYPECODE in (0,1)
union all
--The updated rates
select
--Need to use ASOFDATE from CURRENCYEXCHANGERATE rather than from inserted because the ASOFDATE
--may have been modified by the call to dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET above
CURRENCYEXCHANGERATE.ASOFDATE,
inserted.FROMCURRENCYID,
inserted.TOCURRENCYID
from
inserted
inner join dbo.CURRENCYEXCHANGERATE on inserted.ID = CURRENCYEXCHANGERATE.ID
where
inserted.TYPECODE in (0,1)
) as DATA
--Update the NEXTRATEASOFDATE field on the appropriate rows from DATESTOUPDATE
update
dbo.CURRENCYEXCHANGERATE
set
NEXTRATEASOFDATE = DATA.NEXTRATEASOFDATE,
NEXTRATEASOFDATESDTZ = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(switchoffset(DATA.NEXTRATEASOFDATE,'+00:00')),
CURRENCYEXCHANGERATE.CHANGEDBYID = CURRENCYEXCHANGERATE.CHANGEDBYID,
CURRENCYEXCHANGERATE.DATECHANGED = CURRENCYEXCHANGERATE.DATECHANGED
from
(
select
(
select top 1
DATEADD(ms, -1, NEXTRATE.ASOFDATE)
from
dbo.CURRENCYEXCHANGERATE as NEXTRATE
where
NEXTRATE.FROMCURRENCYID = CURRENCYEXCHANGERATE.FROMCURRENCYID
and NEXTRATE.TOCURRENCYID = CURRENCYEXCHANGERATE.TOCURRENCYID
and NEXTRATE.ASOFDATE > CURRENCYEXCHANGERATE.ASOFDATE
and NEXTRATE.TYPECODE in (0,1)
order by
NEXTRATE.ASOFDATE
) as NEXTRATEASOFDATE,
CURRENCYEXCHANGERATE.ID
from
dbo.CURRENCYEXCHANGERATE
inner join @DATESTOUPDATE as DATESTOUPDATE on
CURRENCYEXCHANGERATE.ASOFDATE = DATESTOUPDATE.ASOFDATE
and CURRENCYEXCHANGERATE.FROMCURRENCYID = DATESTOUPDATE.FROMCURRENCYID
and CURRENCYEXCHANGERATE.TOCURRENCYID = DATESTOUPDATE.TOCURRENCYID
and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
) as DATA
inner join dbo.CURRENCYEXCHANGERATE on DATA.ID = CURRENCYEXCHANGERATE.ID
where
(
CURRENCYEXCHANGERATE.NEXTRATEASOFDATE <> DATA.NEXTRATEASOFDATE
or (CURRENCYEXCHANGERATE.NEXTRATEASOFDATE is null and DATA.NEXTRATEASOFDATE is not null)
or (CURRENCYEXCHANGERATE.NEXTRATEASOFDATE is not null and DATA.NEXTRATEASOFDATE is null)
);
--Update the ISLATESTRATEFORASOFDATE field on the appropriate rows from @DATESTOUPDATE
update
dbo.CURRENCYEXCHANGERATE
set
ISLATESTRATEFORASOFDATE = DATA.ISLATESTRATEFORASOFDATE,
CURRENCYEXCHANGERATE.CHANGEDBYID = CURRENCYEXCHANGERATE.CHANGEDBYID,
CURRENCYEXCHANGERATE.DATECHANGED = CURRENCYEXCHANGERATE.DATECHANGED
from
(
select
case when CURRENCYEXCHANGERATE.ID =
(
select top 1
SAMEDATES.ID
from
dbo.CURRENCYEXCHANGERATE as SAMEDATES
where
SAMEDATES.ASOFDATE = CURRENCYEXCHANGERATE.ASOFDATE
and SAMEDATES.FROMCURRENCYID = CURRENCYEXCHANGERATE.FROMCURRENCYID
and SAMEDATES.TOCURRENCYID = CURRENCYEXCHANGERATE.TOCURRENCYID
and SAMEDATES.TYPECODE in (0,1)
order by
SAMEDATES.DATEADDED desc,
SAMEDATES.DATECHANGED desc,
SAMEDATES.RATE
)
then 1
else 0
end as ISLATESTRATEFORASOFDATE,
CURRENCYEXCHANGERATE.ID
from
dbo.CURRENCYEXCHANGERATE
inner join @DATESTOUPDATE as DATESTOUPDATE on
CURRENCYEXCHANGERATE.ASOFDATE = DATESTOUPDATE.ASOFDATE
and CURRENCYEXCHANGERATE.FROMCURRENCYID = DATESTOUPDATE.FROMCURRENCYID
and CURRENCYEXCHANGERATE.TOCURRENCYID = DATESTOUPDATE.TOCURRENCYID
and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
) as DATA
inner join dbo.CURRENCYEXCHANGERATE on DATA.ID = CURRENCYEXCHANGERATE.ID
where
CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE <> DATA.ISLATESTRATEFORASOFDATE;
end
end
|
Trigger Definition
