Re: Schema Level Trigger difficulties

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 12 Jul 2005 20:11:15 -0700
Message-ID: <1121224275.794422.321960@o13g2000cwo.googlegroups.com>

scott_at_ORA92> CREATE OR REPLACE PROCEDURE CreateMyTableLevelTrigger   2 (p_table IN VARCHAR2)
  3 AS
  4 BEGIN
  5 EXECUTE IMMEDIATE

  6  	 'CREATE OR REPLACE TRIGGER tr_' || p_table
  7  	 || ' BEFORE INSERT ON ' || p_table
  8  	 || ' BEGIN NULL; END;';

  9 END CreateMyTableLevelTrigger;
 10 /

Procedure created.

scott_at_ORA92> SHOW ERRORS
No errors.
scott_at_ORA92> CREATE OR REPLACE TRIGGER after_create_on_schema   2 AFTER CREATE ON SCHEMA
  3 WHEN (ora_dict_obj_type = 'TABLE')   4 BEGIN
  5 CreateMyTableLevelTrigger (ora_dict_obj_name);   6 END after_create_on_schema;
  7 /

Trigger created.

scott_at_ORA92> SHOW ERRORS
No errors.
scott_at_ORA92> CREATE TABLE test_table (col1 NUMBER)   2 /

Table created.

scott_at_ORA92> SELECT object_name
  2 FROM user_objects
  3 WHERE object_name LIKE '%TEST_TABLE'   4 /

OBJECT_NAME

---

TEST_TABLE
TR_TEST_TABLE scott_at_ORA92> Received on Tue Jul 12 2005 - 22:11:15 CDT