Bitemporal triggers

-- testing insert trigger
insert into lST_Stage (
    ST_NAM_Stage_Name,
    ST_NAM_ChangedAt
)
values('Arena', DATEADD(DAY, -1, CURRENT_TIMESTAMP));

select * from lST_Stage;
select * from ST_NAM_Stage_Name;

-- testing update trigger: versioning
update lST_Stage
set
    ST_NAM_Stage_Name = 'Bungalow'
where
    ST_NAM_Stage_Name = 'Arena';

select * from lST_Stage;
select * from pST_Stage(DATEADD(DAY, -1, CURRENT_TIMESTAMP));
select * from ST_NAM_Stage_Name;

-- testing update trigger: no matching row
update lST_Stage
set
    ST_NAM_Stage_Name = 'Center'
where
    ST_NAM_Stage_Name = 'Arena';

select * from ST_NAM_Stage_Name;

-- testing update trigger: correction
update lST_Stage
set
    ST_NAM_Stage_Name = 'Center',
    ST_NAM_ErasedAt = DATEADD(year, 1, CURRENT_TIMESTAMP)
where
    ST_NAM_Stage_Name = 'Bungalow';

select * from lST_Stage;
select * from pST_Stage(DATEADD(DAY, -1, CURRENT_TIMESTAMP));
select * from lpST_Stage(CURRENT_TIMESTAMP);
select * from ST_NAM_Stage_Name;

-- testing update trigger: logical delete
update lST_Stage
set
    ST_NAM_ErasedAt = DATEADD(year, 2, CURRENT_TIMESTAMP)
where
    ST_NAM_Stage_Name = 'Center';

select * from lST_Stage;
select * from lpST_Stage(CURRENT_TIMESTAMP);
select * from lpST_Stage(DATEADD(year, 1, CURRENT_TIMESTAMP));
select * from ST_NAM_Stage_Name;

-- testing (logical) delete trigger
delete
from
    lST_Stage
where
    ST_NAM_Stage_Name = 'Arena';

select * from lST_Stage;
declare @before datetime;
select
    @before = DATEADD(second, -1, ST_NAM_ErasedAt)
from
    ST_NAM_Stage_Name
where
    ST_NAM_Stage_Name = 'Arena';
select * from ppST_Stage(DATEADD(day, -1, CURRENT_TIMESTAMP), @before);
select * from lpST_Stage(CURRENT_TIMESTAMP);
select * from lpST_Stage(DATEADD(year, 1, CURRENT_TIMESTAMP));
select * from ST_NAM_Stage_Name;