Oracle PL/SQL Tutorial/Trigger/Database Level Trigger

Database level trigger: AFTER STARTUP ON DATABASE

   <source lang="sql">

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

Database level trigger: BEFORE SHUTDOWN ON DATABASE

   <source lang="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> 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.</source>

Database logoff trigger

   <source lang="sql">

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.</source>

Database logon trigger

   <source lang="sql">

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.</source>

New Database triggers

   <source lang="sql">

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.</source>

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

   <source lang="sql">

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.</source>