Welcome to the next tutorial of PL/SQL Triggers in Oracle Database. In this tutorial we will learn how to write:

  1. Schema Level Logoff System Event Trigger and
  2. Database Level Logoff System Event Trigger

For this tutorial, knowledge of System/Database Event trigger is required which we have already discussed in the previous tutorial.

Unlike logon database event trigger which fires after a valid logon attempt by a user on the database, logoff triggers execute before the user logs off from the database. Logoff trigger can be proved as a versatile tool for a DBA as well as for a database user.

Schema Level Logoff System Event Trigger

As I explained in the previous tutorial that a schema level trigger is one which worked only for a specific schema in which it is created or designed to work for. Any user of the database who has “Create Trigger” system privilege can design and create this trigger.

Example

Let’s write a trigger to audit the logoff

Step 1: Logon to the database

Logon to the database using any user such as HR, SH, OE or any other you want.

C:\> SQLPLUS hr/hr

Read here: How to connect to the database With Sample User (e.g. HR) using SQL Developer

Step 2: Create a table.

Create a table to dump the data generated by your schema level logoff trigger.

CREATE TABLE hr_evnt_audit
 (
  event_type VARCHAR2(30),
  logon_date DATE,
  logon_time VARCHAR2(15),
  logof_date DATE,
  logof_time VARCHAR2(15)
 );

Step 3: Write the trigger.

Below written trigger will execute every time user HR logs off from the database.

CREATE OR REPLACE TRIGGER log_off_audit
BEFORE LOGOFF ON SCHEMA
BEGIN
  INSERT INTO hr_evnt_audit VALUES(
    ora_sysevent,
    NULL,
    NULL,
    SYSDATE,
    TO_CHAR(sysdate, 'hh24:mi:ss')
  );
  COMMIT;
END;
/

On successful compilation this trigger will fire every time the user, using which it was created, logs off from the database and that user in our case is HR.

As I said above that this trigger is bound to work only for the user in which it is created. What if we want to keep track of all the logoff activities of all the users of the database? In such a scenario we can write a database level system event trigger.

Database Level System/Database Event Trigger.

An often asked question in the interview is what are the differences between Schema Level and Database Level system Event triggers? There can be variety of answers for this question but major differences are as follows.

As the name suggests Database event trigger fires for the entire database or you can say that it fires for all the schemas created under the database in which these triggers are created which is unlike Schema Level System Event trigger which runs for a specific schema only.

Also database level system event trigger can only be created by high privileged users such as sys or system or any user who has ADMINISTER DATABASE TRIGGER system privilege where Schema level system event trigger can be created by any user of the database on its own schema which has Create Trigger System Privilege.

Example: How To Create Database Level Logoff event Trigger In Oracle PL/SQL

Creation process of Database Level Logoff Event Trigger is pretty similar to the trigger which we just saw except for a few minute changes.

Step 1: Logon to the database

As only the user with ADMINISTER DATABASE TRIGGER system privilege can create a database level event trigger thus we need to make sure that this time we should log on to the database using one of these users.

C:\> SQLPLUS / as SYSDBA

Suggested Reading: Make Database Connection With SYS User In SQL Developer.

Step 2: Create a Table

Again in order to store the audit data we need to create a table where this trigger will journal the entries of all the users. The structure of the table will be pretty similar to the above one except one extra column for storing the username with the help of which we can clearly identify the details and avoid the confusion.

CREATE TABLE db_evnt_audit
  (
User_name VARCHAR2(15),
    event_type VARCHAR2(30),
    logon_date DATE,
    logon_time VARCHAR2(15),
    logof_date DATE,
    logof_time VARCHAR2(15)
  );

Step 3: Write the Database Level logoff system event trigger.

Following Trigger will keep an eye on the logoff activity of the user of the database.

CREATE OR REPLACE TRIGGER db_lgof_audit
BEFORE LOGOFF ON DATABASE
BEGIN
  INSERT INTO db_evnt_audit 
  VALUES(
    user,
    ora_sysevent,
    NULL,
    NULL,
    SYSDATE,
    TO_CHAR(sysdate, 'hh24:mi:ss')
    );
END;
/

Code is very similar to the previous one except that this time this trigger will execute for all the users of the database. This we are making sure by using the keyword DATABASE instead of SCHEMA in the second line of the trigger unlike the previous trigger.

There can be various ways of taking advantage of these triggers; it depends on your creativity. All the above examples are meant to teach you the proper way of creating a System/Database Event Trigger.

Hope you learnt something new and enjoyed Reading. I suggest you to head over to my YouTube channel and Watch the video on the same topic and give it a thumbs up. Also share this blog with the hashtag #RebellionRider so that I can give a shoutout. Thanks & have a great day!

RELATED ARTICLESMORE FROM AUTHOR