我有一个表标识,我还有一个表ident_hist,它只保存表ident的日志 . 表标识被大量更改,所以我想动态地将新列添加到ident_hist . 我创建了一个程序来执行以下操作:
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);
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;
for i in c
loop
execute immediate i.lijn;
end loop;
end;
我的问题是,如果我正在改变表格标识,我如何检查我的DDL触发器?
我想做这样的事情:
create or replace trigger ident_hist_trig before alter on ident
begin
prc_create_hist_tabel('ident_hist', 'ident');
end;
当我尝试编译触发器时,我收到此错误消息:
ORA-30506:系统触发器不能基于表或视图
如果我正在改变我的表格身份,我怎样才能检查我的DDL触发器?我只想在改变表格身份时触发它,而不是任何其他表格 .