Schema trigger : Schema Trigger « Trigger « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Trigger
3. 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>