В другом вопросе я попытался создать таблицу истории, которая хранит log из данной таблицы. Получив ответы на этот вопрос, я попытался создать что-то новое.
Поскольку невозможно создать системный триггер для таблиц или представлений, я создал триггер DDL следующим образом:
create or replace trigger ident_hist_trig after alter on schema
declare
v_table varchar2(30);
begin
select upper(ora_dict_obj_name) into v_table from dual;
if (v_table = 'Z_IDENT') then
prc_create_hist_tabel('z_ident_hist', 'z_ident');
elsif (v_table = 'D_IDENT') then
prc_create_hist_tabel('d_ident_hist', 'd_ident');
elsif (v_table = 'X_IDENT') then
prc_create_hist_tabel('x_ident_hist', 'x_ident');
else
null;
end if;
end;
/
Процедура prc_create_hist_tabel выглядит так:
create or replace procedure prc_create_hist_tabel(p_naam_hist_tabel in varchar2, p_naam_tabel in varchar2) is
cursor c is
select 'alter table ' || p_naam_hist_tabel || ' add ' || column_name || ' ' || data_type || case when data_type = 'DATE' then null else '(' || data_length || ')' end lijn
from user_tab_columns
where TABLE_NAME = upper(p_naam_tabel)
and column_name not in (select column_name from user_tab_columns where table_name = upper(p_naam_hist_tabel));
v_dummy number(1);
cursor trig is
select column_name || ',' kolom, ':old.' || column_name || ',' old
from user_tab_columns
where table_name = upper(p_naam_tabel);
v_trigger_sql varchar2(32767);
begin
begin
select 1 into v_dummy
from user_tab_columns
where TABLE_NAME = upper(p_naam_hist_tabel)
group by 1;
exception when no_data_found then
execute immediate 'create table ' || p_naam_hist_tabel || ' (wijziger varchar2(60) default user, wijzigdatum date default sysdate, constraint pk_' || p_naam_hist_tabel || ' primary key (wijziger, wijzigdatum))';
end;
dbms_output.put_line('BBB');
for i in c
loop
begin
dbms_output.put_line(i.lijn);
execute immediate i.lijn;
exception when others then
dbms_output.put_line(i.lijn);
end;
end loop;
v_trigger_sql := 'create or replace trigger ' || p_naam_tabel || '_hist_trig after update on ' || p_naam_tabel || ' for each row begin insert into ' || p_naam_hist_tabel || ' (';
for v_lijn in trig
loop
v_trigger_sql := v_trigger_sql || v_lijn.kolom;
end loop;
v_trigger_sql := substr(v_trigger_sql, 1, length(v_trigger_sql) - 1);
v_trigger_sql := v_trigger_sql || ') values (';
for v_lijn in trig
loop
v_trigger_sql := v_trigger_sql || v_lijn.old;
end loop;
v_trigger_sql := substr(v_trigger_sql, 1, length(v_trigger_sql) - 1);
v_trigger_sql := v_trigger_sql || '); end;';
execute immediate v_trigger_sql;
end;
/
Короче говоря, эта функция поддерживает таблицу истории. Если он не существует, он создаст его, а если он существует, он добавит к нему новые столбцы. Процедура также создает новый триггер, который запишет старые значения в таблицу истории после обновления.
Но когда я изменяю одну из таблиц x_ident, z_ident или d_ident, курсор c ничего не возвращает (я могу проверить это с помощью печати, когда просматриваю ее). Хотя при выполнении выбора после я изменил свою таблицу, я получаю результаты.
Результаты, которые я получаю от изменения таблицы d_ident, таковы:
BBB
d_ident: Table altered.
Но я предполагаю, что должно быть наоборот, я думаю, что процедура prc_create_hist_tabel выполняется до того, как фактически сработает таблица изменений, и я предполагаю, что я должен получить что-то вроде этого:
d_ident: Table altered.
BBB
Любая помощь будет оценена. Я попытался создать триггер для вставки в user_tab_columns, но это дало мне ORA-25001: невозможно создать этот тип триггера для представлений.
Я также пытался использовать команду сна, но это тоже не сработало.