Oracle-триггеры для создания строки истории при обновлении

К сожалению, нет способа избежать ссылки на все имена столбцов (: OLD.этот, старый.что и т. д.) в триггерах. Однако вы можете написать программу для генерации кода триггера из определения таблицы (в USER_TAB_COLS). Затем всякий раз, когда таблица изменяется, вы можете создать и скомпилировать новую копию триггеров.

Смотрите этот поток AskTom для того, как это сделать.

В случае, если у кого-то есть тот же узкоспециализированный случай, что и у нас (Linq access делает историю одной таблицы намного чище/проще, это то, что я сделал, чтобы упростить то, что у нас есть, приветствуйте любые улучшения....это просто скрипт, который будет запускаться всякий раз, когда база данных изменяется, регенерируя триггеры аудита, главным изменением является PRAGMA AUTONOMOUS_TRANSACTION; размещение истории генерации на автономной транзакции и не заботясь о мутации (что не имеет значения для того, как мы проводим аудит):

Declare
  cur_trig varchar(4000);
  has_ver number;
Begin
    For seq in (Select table_name, sequence_name 
              From user_tables ut, user_sequences us
              Where sequence_name = replace(table_name, '_','') || '_SEQ'
                And table_name Not Like '%$%'
                And Exists (Select 1
                            From User_Tab_Columns utc
                            Where Column_Name = 'ID' And ut.table_name = utc.table_name)
                And Exists (Select 1
                            From User_Tab_Columns utc
                            Where Column_Name = 'DATE_START' And ut.table_name = utc.table_name)
                And Exists (Select 1
                            From User_Tab_Columns utc
                            Where Column_Name = 'DATE_MODIFIED' And ut.table_name = utc.table_name))
    Loop
     --ID Insert Triggers (Autonumber for oracle!)
     cur_trig := 'CREATE OR REPLACE TRIGGER ' || seq.table_name || 'CR' || chr(10)
              || 'BEFORE INSERT ON ' || seq.table_name || chr(10)
              || 'FOR EACH ROW' || chr(10)
              || 'BEGIN' || chr(10)
              || '  SELECT ' || seq.sequence_name || '.NEXTVAL INTO :new.ID FROM DUAL;' || chr(10)
              || '  IF(:NEW.ENTITY_ID = 0) THEN' || chr(10)
              || '    SELECT sysdate, sysdate, ''31-DEC-9999'' INTO :NEW.DATE_CREATED, :NEW.DATE_START, :NEW.DATE_MODIFIED FROM DUAL;' || chr(10)
              || '  END IF;' || chr(10)
              || 'END;' || chr(10);

     Execute Immediate cur_trig;

     --History on update Triggers
     cur_trig := 'CREATE OR REPLACE TRIGGER ' || seq.table_name || '_HIST' || chr(10)
              || '  BEFORE UPDATE ON ' || seq.table_name || ' FOR EACH ROW' || chr(10)
              || 'DECLARE' || chr(10)
              || '  PRAGMA AUTONOMOUS_TRANSACTION;' || chr(10)
              || 'BEGIN' || chr(10)
              || '  INSERT INTO ' || seq.table_name || ' (' || chr(10)
              || '   DATE_MODIFIED ' || chr(10)
              || '   ,ENTITY_ID ' || chr(10);

       For col in (Select column_name
                 From user_tab_columns ut
                 Where table_name = seq.table_name
                   And column_name NOT In ('ID','DATE_MODIFIED','ENTITY_ID')
                 Order By column_name)
     Loop
       cur_trig := cur_trig || '   ,' || col.column_name || chr(10);
     End Loop;

     cur_trig := cur_trig || ') VALUES ( --ID is Automatic via another trigger' || chr(10)
                          || '   SYSDATE --DateModified Set' || chr(10)
                          || '   ,:old.ID --EntityID Set' || chr(10);

     has_ver := 0;
       For col in (Select column_name
                 From user_tab_columns ut
                 Where table_name = seq.table_name
                   And column_name NOT In ('ID','DATE_MODIFIED','ENTITY_ID')
                 Order By column_name)
     Loop
       cur_trig := cur_trig || '   ,:old.' || col.column_name || chr(10);
       If Upper(col.column_name) = 'VERSION' Then 
         has_ver := 1; 
       End If;
     End Loop;

     cur_trig := cur_trig || ');' || chr(10)
                          || ':new.DATE_MODIFIED := ''31-DEC-9999'';' || chr(10)
                          || ':new.DATE_START := SYSDATE;' || chr(10);
     If has_ver = 1 Then
       cur_trig := cur_trig || ':new.version := :old.version + 1;' || chr(10);
     End If;
     cur_trig := cur_trig || 'COMMIT;' || chr(10)
                          || 'END;' || chr(10);

     Execute Immediate cur_trig;
    End Loop;
End;
/

Если вы можете улучшить, не стесняйтесь...Я написал всего несколько сценариев PL/SQL, необходимость в них возникает не часто...вероятно, там многое оставляло желать лучшего.

Ответьте должноеAPC за то, что вы заставили меня посмотреть на это немного сложнее. Я не рекомендую этот макет истории, если он не является остальной частью вашей модели/приложения/стека очень хорошо. Для этого приложения мы постоянно показываем сочетание истории и текущего, и фильтрация намного проще, чем объединение, когда речь заходит о Linq-to-SQL доступ к стилю. Спасибо за все ответы, ребята, за все хорошие предложения...и когда у меня будет больше времени, и я не буду стеснен графиком выпуска, это то, к чему я вернусь, чтобы посмотреть, можно ли его улучшить дальше.

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

Для меня это имеет то преимущество, что у меня есть простое " текущее "представление и отдельное, но полностью автоматизированное" аудиторское " представление (которое в этом случае также имеет текущее представление). вид).

Что-то вроде:

create sequence seq_contact start with 1000 increment by 1 nocache nocycle;

create table contact (
    contact_id integer,
    first_name varchar2(120 char),
    last_name varchar2(120 char),
    last_update_date date
    );

alter table contact add constraint pk_contact primary key (contact_id);

create table a$contact (
    version_id integer,
    contact_id integer,
    first_name varchar2(120 char),
    last_name varchar2(120 char),
    last_update_date date
    );

alter table a$contact add constraint pk_a$contact primary key
        (contact_id, version_id);

create or replace trigger trg_contact
before insert or delete or update on contact 
for each row
declare

    v_row contact%rowtype;
    v_audit a$contact%rowtype;

begin

    select seq_contact.nextval into v_audit.version_id from dual;

    if not deleting then

        :new.last_update_date := sysdate;

    end if;

    if inserting or updating then

        v_audit.contact_id := :new.contact_id;
        v_audit.first_name := :new.first_name;
        v_audit.last_name := :new.last_name;
        v_audit.last_update_date := :new.last_update_date;

    elsif deleting then

        v_audit.contact_id := :old.contact_id;
        v_audit.first_name := :old.first_name;
        v_audit.last_name := :old.last_name;
        v_audit.last_update_date := sysdate;

    end if;

    insert into a$contact values v_audit;

end trg_contact;
/

insert into contact (contact_id, first_name, last_name) values
    (1,'Nick','Pierpoint');

insert into contact (contact_id, first_name, last_name) values
    (2, 'John', 'Coltrane');

insert into contact (contact_id, first_name, last_name) values
    (3, 'Sonny', 'Rollins');

insert into contact (contact_id, first_name, last_name) values
    (4, 'Kenny', 'Wheeler');

update contact set last_name = 'Cage' where contact_id = 1;

delete from contact where contact_id = 1;

update contact set first_name = 'Zowie' where contact_id in  (2,3);

select * from a$contact order by contact_id, version_id;

VERSION_ID  CONTACT_ID  FIRST_NAME  LAST_NAME  LAST_UPDATE_DATE
1000        1           Nick        Pierpoint  11/02/2010 14:53:49
1004        1           Nick        Cage       11/02/2010 14:54:00
1005        1           Nick        Cage       11/02/2010 14:54:06
1001        2           John        Coltrane   11/02/2010 14:53:50
1006        2           Zowie       Coltrane   11/02/2010 14:54:42
1002        3           Sonny       Rollins    11/02/2010 14:53:51
1007        3           Zowie       Rollins    11/02/2010 14:54:42
1003        4           Kenny       Wheeler    11/02/2010 14:53:53

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

В зависимости от сложности вашей базы данных (количество таблиц, размер, глубина связей PK/FK, другая логика в триггерах), вы можете посмотреть на Oracle Workspace Management. Вы вызываете API, чтобы поместить таблицу в управление рабочей областью, в результате чего Oracle заменяет таблицу обновляемым представлением и другими соответствующими объектами, которые поддерживают историю всех версий строк.

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

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

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