AFTER CREATE ON SCHEMA : Schema Trigger « Trigger « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Trigger
3. Schema Trigger

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 JAVA2S                         null
null


1 row selected.

SQL>
SQL>
SQL> drop table myAudit;

Table dropped.

SQL> drop trigger after_ddl_creation;

Trigger dropped.

SQL>
SQL>