Oracle:如何确定“更改后"对象中对象的新名称?扳机?

问题描述

假设我的Oracle数据库上有一个AFTER ALTER触发器，并且重命名了一些数据库对象(ALTER ... RENAME TO ...).在触发器中，如何确定数据库对象的新名称?似乎ORA_DICT_OBJ_OWNER，ORA_DICT_OBJ_NAME和ORA_DICT_OBJ_TYPE函数都返回数据库对象的 old 值.

Suppose I have an AFTER ALTER trigger on my Oracle database and I rename some database object (ALTER ... RENAME TO ...). Within the trigger, how do I determine the new name of the database object? It seems that the ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME and ORA_DICT_OBJ_TYPE functions all return the old values of the database object.

例如:

CREATE OR REPLACE TRIGGER ADAM_BEFORE_AFTER BEFORE ALTER ON DATABASE
BEGIN
  DBMS_OUTPUT.put_line('Before alter: ' || ora_dict_obj_owner || '.' || ora_dict_obj_name || ' (' || ora_dict_obj_type || ')');
END;

CREATE OR REPLACE TRIGGER ADAM_AFTER_ALTER AFTER ALTER ON DATABASE
BEGIN
  DBMS_OUTPUT.put_line('After alter: ' || ora_dict_obj_owner || '.' || ora_dict_obj_name || ' (' || ora_dict_obj_type || ')');
END;

假设我重命名表:

ALTER TABLE USELESS_TABLE9 RENAME TO USELESS_TABLE10

数据库输出以下内容:

Before alter: DEVELOPER.USELESS_TABLE9 (TABLE)
After alter: DEVELOPER.USELESS_TABLE9 (TABLE)

更新:不幸的是，我上面显示的输出不正确.输出实际上是由我先前创建的BEFORE DDL触发器和AFTER DDL触发器生成的，而不是是由BEFORE RENAME和AFTER RENAME触发器生成的.我将继续调查为什么BEFORE RENAME和AFTER RENAME触发器没有触发...

Update: Unfortunately, the output I presented above was incorrect. The output was actually being generated by a BEFORE DDL trigger and an AFTER DDL trigger I had created earlier, not by the BEFORE RENAME and AFTER RENAME triggers. I will continue to investigate why the BEFORE RENAME and AFTER RENAME triggers are not firing...

更新:看来BEFORE RENAME和AFTER RENAME触发器拒绝触发，但BEFORE ALTER和AFTER ALTER触发器却触发.我已经相应地更新了问题.

Update: It appears that the BEFORE RENAME and AFTER RENAME triggers refuse to fire, but the BEFORE ALTER and AFTER ALTER triggers do. I have updated the question accordingly.

推荐答案

ALTER RENAME不会触发触发器，RENAME x TO y会触发.

ALTER RENAME won't fire the trigger, RENAME x TO y will.

关于您之前和之后的姓名问题，我认为您必须解析DDL才能检索它们，例如:

As for your question about names before and after, I think you will have to parse the DDL to retrieve them, like that:

CREATE OR REPLACE TRIGGER MK_BEFORE_RENAME BEFORE RENAME ON SCHEMA 
DECLARE 
  sql_text ora_name_list_t;
  v_stmt VARCHAR2(2000);
  n PLS_INTEGER; 
BEGIN  
  n := ora_sql_txt(sql_text);
  FOR i IN 1..n LOOP
   v_stmt := v_stmt || sql_text(i);
  END LOOP;

  Dbms_Output.Put_Line( 'Before: ' || regexp_replace( v_stmt, 'rename[[:space:]]+([a-z0-9_]+)[[:space:]]+to.*', '\1', 1, 1, 'i' ) );
  Dbms_Output.Put_Line( 'After: ' || regexp_replace( v_stmt, 'rename[[:space:]]+.*[[:space:]]+to[[:space:]]+([a-z0-9_]+)', '\1', 1, 1, 'i' ) );
END;

可以肯定地将正则表达式写得更清楚，但是它可以起作用:

The regular expressions could surely be written more clearly, but it works:

RENAME 
mktestx
TO                 mktesty;

Before: mktestx
After: mktesty

更新以适应您更改的问题:

UPDATE To accommodate your changed question:

CREATE OR REPLACE TRIGGER MK_AFTER_ALTER AFTER ALTER ON SCHEMA 
DECLARE 
  sql_text ora_name_list_t;
  v_stmt VARCHAR2(2000);
  n PLS_INTEGER; 
BEGIN  
  n := ora_sql_txt(sql_text);
  FOR i IN 1..n LOOP
   v_stmt := v_stmt || sql_text(i);
  END LOOP;

  Dbms_Output.Put_Line( 'Before: ' || regexp_replace( v_stmt, 'alter[[:space:]]+table[[:space:]]+([a-z0-9_]+)[[:space:]]+rename[[:space:]]+to.*', '\1', 1, 1, 'i' ) );
  Dbms_Output.Put_Line( 'After: ' || regexp_replace( v_stmt, 'alter[[:space:]]+table[[:space:]]+.*to[[:space:]]+([a-z0-9_]+)', '\1', 1, 1, 'i' ) );
END;

这篇关于Oracle:如何确定“更改后"对象中对象的新名称?扳机?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！