Oracle-11g-R2

在事件創建的數據庫觸發器中 - 如何引用創建的對象?

  • November 16, 2018

給定以下觸發器:

CREATE OR REPLACE TRIGGER 
 TRG_CREATE_TABLE_INITRANS
 AFTER CREATE ON SCHEMA
BEGIN
 IF SYS.DICTIONARY_OBJ_TYPE = 'TABLE' THEN
 exec 'alter table '||<table_owner>||'.'||<table_name>||' initrans 10'
 END IF;
END;

如何訪問觸發觸發器的對象所有者和對象名稱?

如果您在架構中創建觸發器,則架構名稱是對象所有者/表所有者,或者您可以使用 sys.dictionary_obj_owner 和表名 sys.dictionary_obj_name。

   SQL> SET SERVEROUTPUT ON
   SQL> SHOW USER
   USER is "OT"
   SQL> CREATE TABLE test01 (sno number);

   Table created.

SQL> SELECT dbms_metadata.get_ddl('TABLE','TEST01','OT') FROM dual;   -- before trigger created

DBMS_METADATA.GET_DDL('TABLE','TEST01','OT')
       --------------------------------------------------------------------------------

    CREATE TABLE "OT"."TEST01"
      (    "SNO" NUMBER
      ) SEGMENT CREATION DEFERRED
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255          -- default INITRANS is 1
    NOCOMPRESS LOGGING
    TABLESPACE "USERS"


   CREATE OR REPLACE TRIGGER 
     TRG_CREATE_TABLE_INITRANS
     AFTER CREATE ON SCHEMA

   BEGIN
      IF SYS.DICTIONARY_OBJ_TYPE = 'TABLE' THEN
         EXECUTE IMMEDIATE 'ALTER TABLE '||sys.dictionary_obj_owner||'.'||sys.dictionary_obj_name||' INITRANS 10';

      END IF;
   END;

   SQL> drop table test01 purge;
   Table dropped.

   SQL> CREATE TABLE test01 (sno number);
   Table created.

   SQL> SELECT dbms_metadata.get_ddl('TABLE','TEST01','OT') FROM dual;

    DBMS_METADATA.GET_DDL('TABLE','TEST01','OT')
       --------------------------------------------------------------------------------    
   CREATE TABLE "OT"."TEST01"
    (    "SNO" NUMBER
    ) SEGMENT CREATION DEFERRED
   PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255    -- INITRANS 10  
   NOCOMPRESS LOGGING
   TABLESPACE "USERS"

引用自:https://dba.stackexchange.com/questions/222677