plsql在"before alter"触发器中获取表 [英] plsql get table in 'before alter' trigger

问题描述

我有一个表ident，也有一个表ident_hist，它只保留该表ident的日志.表ident发生了很大的变化，因此我也想将新列也动态添加到ident_hist中.我创建了一个执行此操作的过程:

I have a table ident, and I also have a table ident_hist, which just keeps a log from the table ident. The table ident gets altered a lot, so I want to add the new columns to ident_hist dynamically as well. I have created a procedure which does that:

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触发器?

My question is, how can I check in my DDL trigger if I'm altering the tabel ident?

我想做这样的事情:

create or replace trigger ident_hist_trig before alter on ident
begin
    prc_create_hist_tabel('ident_hist', 'ident');
end;

当我尝试编译触发器时，出现以下错误消息:

When I try to compile the trigger, I get this error message:

ORA-30506:系统触发器不能基于表或视图

ORA-30506: system triggers cannot be based on tables or views

如果要更改表标识，如何进行DDL触发器检查?我只想在更改表标识而不是其他任何表时触发触发器.

How can I make my DDL trigger check if I'm altering my table ident? I only want to fire the trigger it when I'm altering the table ident, not any other table.

推荐答案

30506，00000，系统触发器不能基于表或视图"

原因:尝试将系统触发器基于表或表 视图.
操作:确保触发器的类型与基准兼容 对象.

30506, 00000, "system triggers cannot be based on tables or views"

Cause: An attempt was made to base a system trigger on a table or a view.
Action: Make sure the type of the trigger is compatible with the base object.

系统触发器未与单个对象关联. 您可以先创建DDL触发器，然后再创建或更改或放置SCHEMA(用户/所有者).然后，您可以过滤对象名称和DDL类型(DROP，ALTER).

System triggers are not associated with individual objects. You can create DDL trigger before before create or alter or drop on SCHEMA(User/Owner). Then you can filter the object names and the DDL types(DROP, ALTER).

Tom在这里对此进行了详细解释. 编写DDL_EVENT触发器

Here Tom has explained about this in detail. Writting DDL_EVENT Triggers

这篇关于plsql在"before alter"触发器中获取表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！