本文介绍了Oracle:如何使用DDL触发器记录表重命名事件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我将以下DDL触发器用于所有DDL事件.但是在表重命名的情况下将不会执行该操作.
I am using the following DDL Trigger for all DDL Event. But it is not going to be executed in case of the table rename.
我的DDL语句是:
将变更表"ABC"重命名为"ABC1"
Alter table "ABC" rename to "ABC1"
CREATE OR REPLACE TRIGGER AUDIT_DDL_TRIGGER AFTER ddl ON schema
DECLARE
BEGIN
INSERT
INTO DD_DB_OBJECT_DDL_LOG
(
LAST_UPD_TS,
osuser,
current_user,
host,
terminal,
owner,
type,
name,
sysevent
)
VALUES
(
CURRENT_TIMESTAMP,
sys_context('USERENV','OS_USER') ,
sys_context('USERENV','CURRENT_USER') ,
sys_context('USERENV','HOST') ,
sys_context('USERENV','TERMINAL') ,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_dict_obj_name,
ora_sysevent
);
END;
请向我建议解决方案.
推荐答案
您在创建触发器时使用了AFTER DDL
关键字.用户AFTER RENAME
关键字,用于在重命名数据库对象时触发.
You have used AFTER DDL
keyword while creating trigger. User AFTER RENAME
keyword for triggering while rename database object.
示例
create or replace TRIGGER AUDIT_DDL_TRIGGER AFTER ddl or ALTER OR RENAME ON schema
DECLARE
sql_text ora_name_list_t;
n pls_integer;
IS_ALLOWED_TBL_TYPE NUMBER(1) := 0;
IP_PREFIX VARCHAR(50);
V_OLD_NAME VARCHAR2(30);
V_NEW_NAME VARCHAR2(30);
v_stmt VARCHAR(32000);
BEGIN
n := ora_sql_txt(sql_text);
FOR i IN 1..n LOOP
v_stmt := v_stmt || sql_text(i);
END LOOP;
V_OLD_NAME:=regexp_replace( v_stmt, 'rename[[:space:]]+([a-z0-9_]+)[[:space:]]+to.*', '\1', 1, 1, 'i' );
V_NEW_NAME:=regexp_replace( v_stmt, 'rename[[:space:]]+.*[[:space:]]+to[[:space:]]+([a-z0-9_]+)', '\1', 1, 1, 'i' );
Dbms_Output.Put_Line( 'Old Name: ' || V_OLD_NAME);
Dbms_Output.Put_Line( 'New Name: ' || V_NEW_NAME);
END;
这篇关于Oracle:如何使用DDL触发器记录表重命名事件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!