create table my_data_history (
  data_id     int generated always as identity,
  some_data   varchar2(10),
  start_date  date not null,
  end_date    date,
  period for current_data (start_date, end_date),
  constraint my_data_history_pk primary key (data_id, start_date),
  constraint my_data_history_valid_date_range check ( start_date < end_date )
);

create view my_data
as
select *
from my_data_history
  as of period for current_data( sysdate );

create or replace trigger my_data_trg
instead of insert or update or delete
on my_data
for each row
begin
  if deleting
  then
    update my_data_history set end_date = sysdate
    where my_data_id = :old.my_data_id and start_date = :old.start_date;
  end if;

  if updating or inserting
  then
    if :old.my_data_id <> :new.my_data_id
    then
      raise_application_error( -20000, 'This method does not support modifying PK' );
    end if;

    if inserting and ( :new.my_data_id is not null or :new.start_date is not null or :new.end_date is not null)
    then
      raise_application_error( -20001, q'[No thank you! We've alrady got one (DATA_ID, START_DATE, END_DATE)]' );
    end if;

    if updating and :new.end_date is not null
    then
      raise_application_error( -20002, q'[Don't set END_DATE. use DELETE instead]' );
    end if;

    if updating and :new.start_date <> :old.start_date
    then
      raise_application_error( -20002, q'[YOU aren't allowed to modify the START_DATE]' );
    end if;




    merge into my_data_history a
    using (
      select my_data_id, some_data, start_date, end_date
      from my_data_history
      where my_data_id = :old.my_data_id and start_date = :old.start_date
      union all
      select :new.my_data_id my_data_id, :new.some_data some_data
         ,sysdate start_date, null end_date
      from dual
    ) b
    on (a.my_data_id = b.my_data_id and a.start_date = b.start_date)
    when matched then update set a.some_data=b.some_data, a.end_date = sysdate
    when not matched then insert (my_data_id, some_data, start_date)
       values ( nvl(b.my_data_id, my_data_seq.nextval), b.some_data, b.start_date);
  end if;
end;

create sequence my_data_seq;

create table my_data_history (
  surrogate_key int  generated always as identity,
  my_data_id  int default on null my_data_seq.nextval not null,
  some_data   varchar2(10),
  start_date  date not null,
  end_date    date,
  period for current_data (start_date, end_date),
  constraint my_data_history_pk primary key (surrogate_key),
  constraint my_data_history_unique_identifier unique (my_data_id, start_date),
  constraint my_data_history_valid_date_range check ( start_date < end_date )
);

create view my_data
as
select my_data_id, some_data, start_date, end_date
from my_data_history
  as of period for current_data( sysdate );

create or replace trigger my_data_trg
instead of insert or update or delete
on my_data
for each row
begin
  if deleting
  then
    update my_data_history set end_date = sysdate
    where my_data_id = :old.my_data_id and start_date = :old.start_date;
  end if;

  if updating or inserting
  then
    if :old.my_data_id <> :new.my_data_id
    then
      raise_application_error( -20000, 'This method does not support modifying PK' );
    end if;

    if inserting and ( :new.my_data_id is not null or :new.start_date is not null or :new.end_date is not null)
    then
      raise_application_error( -20001, q'[No thank you! We've alrady got one (DATA_ID, START_DATE, END_DATE)]' );
    end if;

    if updating and :new.end_date is not null
    then
      raise_application_error( -20002, q'[Don't set END_DATE. use DELETE instead]' );
    end if;

    if updating and :new.start_date <> :old.start_date
    then
      raise_application_error( -20002, q'[YOU aren't allowed to modify the START_DATE]' );
    end if;




    merge into my_data_history a
    using (
      select my_data_id, some_data, start_date, end_date
      from my_data_history
      where my_data_id = :old.my_data_id and start_date = :old.start_date
      union all
      select :new.my_data_id my_data_id, :new.some_data some_data
         ,sysdate start_date, null end_date
      from dual
    ) b
    on (a.my_data_id = b.my_data_id and a.start_date = b.start_date)
    when matched then update set a.some_data=b.some_data, a.end_date = sysdate
    when not matched then insert (my_data_id, some_data, start_date)
       values ( nvl(b.my_data_id, my_data_seq.nextval), b.some_data, b.start_date);
  end if;
end;