Таблица истории, ссылающаяся на другие значения в таблице/доступ к переменным таблицы пакета

После некоторого размышления я должен пойти с @tbone в этом вопросе. По моему опыту, таблица истории должна быть копией данных в «настоящей» таблице с добавленными полями, чтобы показать, когда действовала конкретная «версия» данных, отображаемых строкой в ​​таблице истории. Итак, если «настоящая» таблица похожа на

CREATE TABLE REAL_TABLE
  (ID_REAL_TABLE    NUMBER PRIMARY KEY,
   COL2             NUMBER,
   COL3             VARCHAR2(50));

то я бы создал таблицу истории как

CREATE TABLE HIST_TABLE
  (ID_HIST_TABLE       NUMBER PRIMARY KEY
   ID_REAL_TABLE       NUMBER,
   COL2                NUMBER,
   COL3                VARCHAR2(50),
   EFFECTIVE_START_DT  TIMESTAMP(9) NOT NULL,
   EFFECTIVE_END_DT    TIMESTAMP(9));

и у меня были бы следующие триггеры, чтобы заполнить все:

CREATE TRIGGER REAL_TABLE_BI
  BEFORE INSERT ON REAL_TABLE
  REFERENCING OLD AS OLD
              NEW AS NEW
  FOR EACH ROW
BEGIN
  IF :NEW.ID_REAL_TABLE IS NULL THEN
    :NEW.ID_REAL_TABLE := REAL_TABLE_SEQUENCE.NEXTVAL;
  END IF;
END REAL_TABLE_BI;

CREATE TRIGGER HIST_TABLE_BI
  BEFORE INSERT ON HIST_TABLE
  FOR EACH ROW
BEGIN
  IF :NEW.ID_HIST_TABLE IS NULL THEN
    :NEW.ID_HIST_TABLE := HIST_TABLE_SEQUENCE.NEXTVAL;
  END IF;
END HIST_TABLE_BI;

CREATE TRIGGER REAL_TABLE_AIUD
  AFTER INSERT OR UPDATE OR DELETE ON REAL_TABLE
  FOR EACH ROW
DECLARE
  tsEffective_start_date TIMESTAMP(9) := SYSTIMESTAMP;
  tsEffective_end_date   TIMESTAMP(9) := dtEffective_start_date - INTERVAL '0.000000001' SECOND;
BEGIN
  IF UPDATING OR DELETING THEN
    UPDATE HIST_TABLE
      SET EFFECTIVE_END_DATE := tsEffective_end_date
      WHERE ID_REAL_TABLE = :NEW.ID_REAL_TABLE AND
            EFFECTIVE_END_DATE IS NULL;
  END IF;

  IF INSERTING OR UPDATING THEN
    INSERT INTO HIST_TABLE (ID_REAL_TABLE, COL2, COL3, EFFECTIVE_START_DATE)
      VALUES (:NEW.ID_REAL_TABLE, :NEW.COL2, :NEW.COL3, tsEffective_start_date);
  END IF;
END REAL_TABLE_AIUD;

Используя этот метод, в таблице «история» есть все исторические версии данных в «настоящей» таблице ПЛЮС полная копия «текущих» данных из «настоящей» таблицы; это делается для упрощения запросов, которые должны сообщать обо всех версиях данных в таблице вплоть до текущих значений включительно.

Преимущество использования триггеров для всего этого заключается в том, что обслуживание первичных ключей и таблицы истории становится автоматическим, и его нельзя легко обойти или забыть.

Делитесь и наслаждайтесь.

Извините, что так медленно возвращаюсь; это заняло у меня немного возни, и у меня не было много времени, чтобы поработать над этим.

Спасибо Бобу Джарвису за то, что он указал мне на составные триггеры, которые значительно очистили общую структуру. После этого мне просто нужно было очистить способ, которым я возвращаю значения из своей табличной переменной. На случай, если кто-то еще наткнется на это в поисках ответа на ту же проблему, я опубликую свое окончательное решение здесь:

create or replace 
trigger pg_machine_in_use_update
  for insert or update or delete of in_use,lablocid
  on labstats_adm.pg_machine
  compound trigger

  type arr is table of number index by binary_integer;
  changedlabids arr;
  idx binary_integer;

  after each row is
    newlabid labstats_adm.pg_labs.labid%TYPE;
    oldlabid labstats_adm.pg_labs.labid%TYPE;
  begin
    -- store the labids of any changed locations
    -- PL/SQL does not like us testing for the existence of something that isn't there, so just set it twice if necessary
    if ( :new.lablocid is not null ) then
      select labid into newlabid from labstats_adm.pg_lablocation where lablocid = :new.lablocid;
      changedlabids( newlabid ) := 1;
    end if;
    if ( :old.lablocid is not null ) then
      select labid into oldlabid from labstats_adm.pg_lablocation where lablocid = :old.lablocid;
      changedlabids( oldlabid ) := 1;
    end if;
  end after each row;

  after statement is
  begin
    idx := changedlabids.FIRST;
    while idx is not null loop
      insert into labstats_adm.pg_lab_history (labid, time, total_seats, used_seats)
        select labid, systimestamp, total_seats, used_seats
          from labstats_adm.lab_usage
          where labid = idx;
      idx := changedlabids.NEXT(idx);
    end loop;
  end after statement;

end pg_machine_in_use_update;