DDL Trigger

DDL triggers are the triggers which are created over DDL statements such as CREATE, DROP or ALTER.DDL triggers execute every time a DDL statement is executed.Using this type of trigger you can monitor the behavior and force rules on your DDL statements.

In order to proceed ahead and start writing the trigger first we need a table in which we can capture the auditing information created by the trigger.

CREATE  TABLE schema_audit
 (
  ddl_date      DATE,
  ddl_user      VARCHAR2(15),
  object_created    VARCHAR2(15),
  object_name     VARCHAR2(15),
  ddl_operation    VARCHAR2(15)
);

DDL Trigger for Schema Auditing:

CREATE OR REPLACE TRIGGER sch_audit_tr 
 AFTER DDL ON SCHEMA
 BEGIN
  INSERT INTO schema_audit VALUES 
  (
   sysdate,
   sys_context('USERENV','CURRENT_USER'),
   ora_dict_obj_type, 
   ora_dict_obj_name,
   ora_sysevent
  );
 END;
 /

"AFTER DDL ON SCHEMA" indicates that this trigger will work on the schema in which it is created.

DDL Trigger for Database Auditing:

  CREATE OR REPLACE TRIGGER db_audit_tr
 AFTER DDL ON DATABASE
 BEGIN
  INSERT INTO schema_audit VALUES 
  (
   sysdate,
   sys_context('USERENV','CURRENT_USER'),
   ora_dict_obj_type, 
   ora_dict_obj_name,
   ora_sysevent
  );
 END;
 /

DDL Triggers for LOGON/LOGOFF:

 CREATE OR REPLACE TRIGGER ddl_logon
   AFTER LOGON ON DATABASE
BEGIN
   INSERT INTO schema_audit(object_name,ddl_date)
        VALUES (   ora_dict_obj_name
                || '-'
                || ora_login_user
                || '-'
                || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI AM')
                || '-'
                || ora_sysevent
                || '-'
                || ora_dict_obj_type
                || '-'
                || ora_dict_obj_owner
                || '-'
                || ora_dict_obj_name,SYSDATE);
END;

 CREATE OR REPLACE TRIGGER ddl_trigger3
   AFTER LOGOFF ON DATABASE
BEGIN
   INSERT INTO schema_audit
               (object_name,
                ddl_date
               )
        VALUES (   ora_dict_obj_name
                || '-'
                || ora_login_user
                || '-'
                || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI AM')
                || '-'
                || ora_sysevent
                || '-'
                || ora_dict_obj_type
                || '-'
                || ora_dict_obj_owner
                || '-'
                || ora_dict_obj_name,
                SYSDATE
               );
END;
/