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 RNTSOFT null
null
1 row selected.
SQL>
SQL> drop table myAudit;
Table dropped.
SQL> drop trigger after_ddl_creation;
Trigger dropped.
SQL>