Oracle PL/SQL Tutorial/Trigger/Database Level Trigger

Database level trigger: AFTER STARTUP ON DATABASE

SQL>
SQL>
SQL> CREATE TABLE uptime_log (
  2      database_name       VARCHAR2(30),
  3      event_name          VARCHAR2(20),
  4      event_time          DATE,
  5      triggered_by_user   VARCHAR2(30)
  6  );
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER log_startup
  2  AFTER STARTUP ON DATABASE
  3  BEGIN
  4      INSERT INTO uptime_log
  5          (database_name,
  6           event_name,
  7           event_time,
  8           triggered_by_user)
  9          VALUES (sys.database_name,
 10                  sys.sysevent,
 11                  sysdate,
 12                  sys.login_user);
 13      COMMIT;
 14  END;
 15  /
Trigger created.
SQL>
SQL> drop table uptime_log;
Table dropped.
SQL>

Database level trigger: BEFORE SHUTDOWN ON DATABASE

SQL>
SQL> CREATE TABLE uptime_log (
  2      database_name       VARCHAR2(30),
  3      event_name          VARCHAR2(20),
  4      event_time          DATE,
  5      triggered_by_user   VARCHAR2(30)
  6  );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER log_shutdown
  2  BEFORE SHUTDOWN ON DATABASE
  3  BEGIN
  4      INSERT INTO uptime_log
  5          (database_name,
  6           event_name,
  7           event_time,
  8           triggered_by_user)
  9          VALUES (sys.database_name,
 10                  sys.sysevent,
 11                  sysdate,
 12                  sys.login_user);
 13      COMMIT;
 14  END;
 15  /
Trigger created.
SQL>
SQL> drop table uptime_log;
Table dropped.

Database logoff trigger

SQL>
SQL> CREATE TABLE session_logon_statistics
  2  (user_logged VARCHAR2(30),
  3  start_time   DATE,
  4  end_time     DATE);
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER logoff_log_trigger
  2  BEFORE LOGOFF
  3  ON DATABASE
  4  BEGIN
  5     UPDATE session_logon_statistics
  6     SET    end_time    = SYSDATE
  7     WHERE  user_logged = USER
  8     AND    end_time IS NULL;
  9  END;
 10  /
Trigger created.
SQL>
SQL> SELECT user_logged,
  2         TO_CHAR(start_time, "MM/DD/YYYY HH24:MI:SS") "START TIME",
  3         TO_CHAR(end_time, "MM/DD/YYYY HH24:MI:SS") "END TIME"
  4  FROM   session_logon_statistics
  5  where  rownum < 50
  6  order by user_logged, start_time;
no rows selected
SQL>
SQL> drop trigger logoff_log_trigger;
Trigger dropped.
SQL>
SQL> drop table session_logon_statistics;
Table dropped.

Database logon trigger

SQL>
SQL> CREATE TABLE session_logon_statistics
  2  (user_logged VARCHAR2(30),
  3  start_time   DATE,
  4  end_time     DATE);
Table created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER logon_log_trigger
  2  AFTER LOGON
  3  ON DATABASE
  4  BEGIN
  5     INSERT INTO session_logon_statistics
  6     (user_logged, start_time)
  7     VALUES
  8     (USER, SYSDATE);
  9  END;
 10  /
Trigger created.
SQL>
SQL>
SQL>
SQL> SELECT user_logged,
  2         TO_CHAR(start_time, "MM/DD/YYYY HH24:MI:SS") "START TIME",
  3         TO_CHAR(end_time, "MM/DD/YYYY HH24:MI:SS") "END TIME"
  4  FROM   session_logon_statistics
  5  where   rownum < 50
  6  order by user_logged, start_time;
no rows selected
SQL>
SQL>
SQL> drop trigger logon_log_trigger;
Trigger dropped.
SQL>
SQL> drop table session_logon_statistics;
Table dropped.

New Database triggers

SQL> CREATE OR REPLACE TRIGGER On_Logon
  2  AFTER LOGON
  3  ON SCHEMA
  4  BEGIN
  5    DBMS_UTILITY.ANALYZE_SCHEMA(sys.login_user,"ESTIMATE");
  6  END;
  7  /
Trigger created.
SQL>
SQL> drop trigger On_Logon;
Trigger dropped.

Save information about all errors in an after-servererror on database

SQL> create table userLog
  2  (error_date date,
  3   username varchar2(30),
  4   error_msg varchar2(2000),
  5   error_sql varchar2(2000));
Table created.
SQL>
SQL> create or replace trigger log_errors after servererror on database
  2  declare
  3     sql_text ora_name_list_t;
  4     msg varchar2(2000) := null;
  5     stmt varchar2(2000):= null;
  6  begin
  7     for i in 1 .. ora_server_error_depth loop
  8        msg := msg||ora_server_error_msg(i);
  9     end loop;
 10     for i in 1..ora_sql_txt(sql_text) loop
 11        stmt := stmt||sql_text(i);
 12     end loop;
 13     insert into userLog(error_date,username,error_msg,error_sql)values (sysdate,ora_login_user,msg,stmt);
 14  end;
 15  /
Trigger created.
SQL>
SQL>
SQL>
SQL> drop table userLog;
Table dropped.