Mega Code Archive

 

Categories / Oracle PLSQL / Trigger

 

Trigger to log the schema altercation

SQL> 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> SQL> CREATE OR REPLACE TRIGGER audit_schema_changes   2  AFTER ALTER ON 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  / Trigger created. SQL> SQL> drop table ALTER_AUDIT_TRAIL; Table dropped. SQL> SQL> drop trigger AUDIT_SCHEMA_CHANGES; Trigger dropped. SQL> --