0

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?

2

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"
  • Thank you so much for that. Would you happen to know where the documentation for that is? I've spent a lot of time in the create trigger doc but it looks like it's missing a lot even with the 3 examples they have. – Nicolas de Fontenay Nov 16 '18 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.