System Triggers

System Triggers

The syntax for creating a system trigger is very similar to the syntax used for creating DML trigger

SQL> CREATE TABLE session_info
  2  (username VARCHAR2(30),
  3  logon_date DATE,
  4  session_id VARCHAR2(30),
  5  ip_addr VARCHAR2(30),
  6  hostname VARCHAR2(30),
  7  auth_type VARCHAR2(30));

Table created.

Here is the code for the BEFORE LOGOFF trigger:

SQL> CREATE OR REPLACE TRIGGER trg_session_info
  2  BEFORE LOGOFF
  3  ON DATABASE
  4  DECLARE
  5   session_id VARCHAR2(30);
  6   ip_addr    VARCHAR2(30);
  7   hostname   VARCHAR2(30);
  8   auth_type  VARCHAR2(30);
  9  BEGIN
 10   SELECT sys_context ('USERENV', 'SESSIONID')
 11   INTO session_id
 12   FROM dual;
 13  
 14   SELECT sys_context ('USERENV', 'IP_ADDRESS')
 15   INTO ip_addr
 16   FROM dual;
 17   
 18   SELECT sys_context ('USERENV', 'HOST')
 19   INTO hostname
 20   FROM dual;
 21   
 22   SELECT sys_context ('USERENV', 'AUTHENTICATION_TYPE')
 23   INTO auth_type
 24   FROM dual;
 25   
 26   INSERT INTO session_info VALUES
 27   (user, sysdate, session_id, ip_addr, hostname, auth_type);
 28  END;
 29  /