主页 / dba / 问题 / 222677
Accepted
Nicolas de Fontenay
Asked: 2018-11-16 15:10:02 +0800 CST

在事件创建的数据库触发器中 - 如何引用创建的对象？

  • 772

给定以下触发器：

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;

如何访问触发触发器的对象所有者和对象名称？

oracle-11g oracle-11g-r2

1 个回答

  1. Best Answer

    如果您在模式中创建触发器，则模式名称是对象所有者/表所有者，或者您可以使用 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"
    • 1

相关问题