Oracle PL/SQL/Trigger/Schema Trigger

log drop trigger on table schema

   <source lang="sql">

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>

</source>
   
  

Logon schema logon to a table

   <source lang="sql">

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>

</source>
   
  

Trigger on schema creation

   <source lang="sql">

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>

</source>
   
  

trigger to log the schema altercation

   <source lang="sql">

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

</source>