Oracle PL/SQL/Trigger/Schema Trigger

log drop trigger on table schema

 
SQL>
SQL>
SQL> create table dropped_objects(
  2    object_name varchar2(30),
  3    object_type varchar2(30),
  4    dropped_on date );
Table created.
SQL>
SQL> create or replace
  2    trigger log_drop_trigger
  3    before drop
  4      on scott.schema
  5  begin
  6    insert into dropped_objects
  7    values ( ora_dict_obj_name,
  8             ora_dict_obj_type,
  9             sysdate );
 10  end;
 11  /
SQL>
SQL> create table drop_me(x number );
Table created.
SQL>
SQL> create view drop_me_view as select * from drop_me;
View created.
SQL>
SQL> drop view drop_me_view;
View dropped.
SQL>
SQL> drop table drop_me;
Table dropped.
SQL>
SQL> select * from dropped_objects;
no rows selected
SQL>
SQL> drop table dropped_objects;
Table dropped.
SQL>
SQL>

Logon schema logon to a table

 
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER LogUserAConnects
  2    AFTER LOGON ON SCHEMA
  3  BEGIN
  4    INSERT INTO MyTable
  5      VALUES (1, "LogUserAConnects fired!");
  6  END LogUserAConnects;
  7  /
Trigger created.
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>

Trigger on schema creation

 
SQL>
SQL> CREATE OR REPLACE TRIGGER town_crier
  2  AFTER CREATE ON SCHEMA
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE("I believe you have created something!");
  5  END;
  6  /
Trigger created.
SQL>
SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE a_table
  2  (col1 NUMBER);
I believe you have created something!
Table created.
SQL>
SQL> DROP TABLE a_table;
Table dropped.
SQL>

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