In a database trigger on event create – How to reference the created object

oracle-11goracle-11g-r2

Given the following trigger:

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;

How do I access the object owner and the object name which triggered the trigger?

Best Answer

If you're creating trigger in a schema then schema name is the object owner/table owner or you can use sys.dictionary_obj_owner and for table name 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"
Related Question