Oracle:如何使用DDL触发器记录表重命名事件 [英] Oracle : How to Log Table Rename Event using DDL Trigger

问题描述

我将以下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触发器记录表重命名事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！