Oracle PL/SQL Tutorial/Trigger/Schema Trigger

AFTER CREATE ON SCHEMA

SQL>
SQL> CREATE TABLE myAudit(
  2   object_name VARCHAR2(30)NOT NULL,
  3   Object_type VARCHAR2(30)NOT NULL,
  4   WHEN_CREATED DATE NOT NULL,
  5   WHO_CREATED VARCHAR2(30)NOT NULL,
  6   WHEN_UPDATED DATE,
  7   WHO_UPDATED VARCHAR2(30)
  8  );
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER after_ddl_creation
  2  AFTER CREATE ON SCHEMA
  3  BEGIN
  4    INSERT INTO myAudit VALUES
  5    (SYS.DICTIONARY_OBJ_NAME,SYS.DICTIONARY_OBJ_TYPE,SYSDATE,USER,NULL,NULL);
  6  END;
  7  /
Trigger created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE p_dummy
  2  IS
  3  BEGIN
  4    NULL;
  5  END;
  6  /
Procedure created.
SQL>
SQL> SELECT * FROM myAudit;
OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------
WHEN_CREAT WHO_CREATED                    WHEN_UPDAT
---------- ------------------------------ ----------
WHO_UPDATED
------------------------------
P_DUMMY                        PROCEDURE
24-07-2008 sqle                         null
null

1 row selected.
SQL>
SQL>
SQL> drop table myAudit;
Table dropped.
SQL> drop trigger after_ddl_creation;
Trigger dropped.
SQL>
SQL>

AFTER DDL ON SCHEMA

SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER no_create
  2  AFTER DDL ON SCHEMA
  3  BEGIN
  4    IF ORA_SYSEVENT = "CREATE" THEN
  5      RAISE_APPLICATION_ERROR(-20000,"Cannot create the " || ORA_DICT_OBJ_TYPE ||
  6                                     " named "            || ORA_DICT_OBJ_NAME ||
  7                                     " as requested by "  || ORA_DICT_OBJ_OWNER);
  8    END IF;
  9  END;
 10  /
Trigger created.
SQL>
SQL>
SQL> drop trigger no_create;
Trigger dropped.

AFTER SUSPEND ON SCHEMA

SQL>
SQL> CREATE OR REPLACE TRIGGER after_suspend
  2  AFTER SUSPEND ON SCHEMA
  3  DECLARE
  4    CURSOR curs_get_extents IS
  5    SELECT max_extents + 1
  6      FROM user_tables
  7     WHERE table_name = "MONTHLY_SUMMARY";
  8    v_new_max NUMBER;
  9  BEGIN
 10    OPEN curs_get_extents;
 11    FETCH curs_get_extents INTO v_new_max;
 12    CLOSE curs_get_extents;
 13    EXECUTE IMMEDIATE "ALTER TABLE MONTHLY_SUMMARY " ||
 14                      "STORAGE ( MAXEXTENTS "        ||
 15                      v_new_max                      || ")";
 16    DBMS_OUTPUT.PUT_LINE("Incremented MAXEXTENTS to " || v_new_max);
 17  END;
 18  /
Trigger created.
SQL> SHO ERR
No errors.
SQL>
SQL>
SQL>
SQL> drop trigger after_suspend;
Trigger dropped.
SQL>

ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME

SQL>
SQL> CREATE OR REPLACE TRIGGER what_column
  2  AFTER ALTER ON SCHEMA
  3  DECLARE
  4    -- cursor to get columns in a table
  5    CURSOR curs_get_columns ( cp_owner VARCHAR2,
  6                              cp_table VARCHAR2 ) IS
  7    SELECT column_name
  8      FROM all_tab_columns
  9     WHERE owner = cp_owner
 10       AND table_name = cp_table;
 11
 12  BEGIN
 13
 14    IF ORA_DICT_OBJ_TYPE = "TABLE" THEN
 15
 16      FOR v_column_rec IN curs_get_columns(ORA_DICT_OBJ_OWNER,
 17                                           ORA_DICT_OBJ_NAME) LOOP
 18
 19        IF ORA_IS_ALTER_COLUMN(v_column_rec.column_name) THEN
 20
 21          DBMS_OUTPUT.PUT_LINE(ORA_DICT_OBJ_OWNER        || "." ||
 22                               ORA_DICT_OBJ_NAME         || "." ||
 23                               v_column_rec.column_name);
 24
 25        END IF;
 26
 27      END LOOP;
 28
 29    END IF;
 30
 31  END;
 32  /
Trigger created.

Schema trigger

SQL> CREATE TABLE ALTER_AUDIT_TRAIL (
  2      object_owner        VARCHAR2(30),
  3      object_name         VARCHAR2(30),
  4      object_type         VARCHAR2(20),
  5      altered_by_user     VARCHAR2(30),
  6      alteration_time     DATE
  7      );
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER audit_schema_changes
  2  AFTER ALTER ON jeff.SCHEMA
  3  BEGIN
  4      INSERT INTO alter_audit_trail
  5          (object_owner,
  6           object_name,
  7           object_type,
  8           altered_by_user,
  9           alteration_time
 10           )
 11          VALUES (sys.dictionary_obj_owner,
 12                  sys.dictionary_obj_name,
 13                  sys.dictionary_obj_type,
 14                  sys.login_user,
 15                  sysdate);
 16  END;
 17  /
SQL> Drop TABLE ALTER_AUDIT_TRAIL;
Table dropped.
SQL>

use event attributes to provide more info

SQL>
SQL> CREATE OR REPLACE TRIGGER town_crier
  2  AFTER CREATE ON SCHEMA
  3  BEGIN
  4
  5    DBMS_OUTPUT.PUT_LINE("I believe you have created a " ||
  6                         ORA_DICT_OBJ_TYPE || " called " ||
  7                         ORA_DICT_OBJ_NAME);
  8  END;
  9  /
Trigger created.
SQL>
SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE a_table
  2  (col1 NUMBER);
I believe you have created a TABLE called A_TABLE
Table created.
SQL>
SQL>
SQL> CREATE INDEX an_index ON a_table(col1);
I believe you have created a INDEX called AN_INDEX
Index created.
SQL>
SQL> drop index an_index;
Index dropped.
SQL>
SQL>
SQL> CREATE FUNCTION a_function RETURN BOOLEAN AS
  2  BEGIN
  3    RETURN(TRUE);
  4  END;
  5  /
I believe you have created a FUNCTION called A_FUNCTION
Function created.
SQL>
SQL> DROP FUNCTION a_function;
Function dropped.
SQL>
SQL> EXEC DBMS_OUTPUT.PUT_LINE(CHR(10));

PL/SQL procedure successfully completed.
SQL>
SQL> drop trigger town_crier;
Trigger dropped.
SQL>
SQL> DROP TABLE a_table;
Table dropped.