Oracle-11g-R2
在事件創建的數據庫觸發器中 - 如何引用創建的對象?
給定以下觸發器:
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"