System Event Triggers in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

System Event Triggers in Oracle with Examples

In this article, I am going to discuss System Event Triggers in Oracle with Examples. Please read our previous article where we discussed Creating DDL Triggers in Oracle with Examples.

System Event Triggers in Oracle

Until now we have learned how to create a trigger on the table, schema, and view. Now, let’s try to learn how to create a trigger on the database. Previously we have created a trigger on DDL statements. We will use the same trigger using the database clause. This is called Triggering a user event.

We will try to learn about trigger databases or system events. This manages the shutting down or starting of the database. There are multiple database trigger events.

Database Event	Trigger Fires When
AFTER SERVER ERROR	An oracle error is raised
After Login	A user logs on to the database (Schema level also)
Before Log off	A user logs off the database (Schema level also)
After startup	The database is opened
Before Shutdown	The database is shutdown normally

Let us try to do an exercise and learn about the system-level triggers. Let us go ahead and connect to the database as sysdba.

Step 1:

Connecting to the database as sys as sysdba

Let’s connect to the container database jayapdb.

Command: alter session set container=jayapdb;

Step 2: Create a table

Let us go ahead and create a table that stores the details of the user logged in.

create table log_table
(
 user_id varchar2(100),
 log_date date,
 action varchar2(100)
 );

We are creating a table with the name log_table with columns user_id, log_date, and action. The user_id will store the details of the user logged in and log_date will store the system date. The action column will store if the user is logged in or logged off. Let’s go ahead and create the table.

So, the table log_table is created.

Step 3: Create two triggers.

We will create two triggers. One trigger will store the details of the user logged in and the other trigger will store the details of the user logged out.

create or replace trigger logon_t
after 
logon
on database
begin
insert into log_table values (user,sysdate,'logon');
end;

We have created the trigger with the name logon_t and the event is logon and the timing is after. The logic is if a user logged in to the database, then the username and system date, and “logon” is stored in the table log_table. Let’s go ahead and compile the trigger.

The trigger logon_t is compiled successfully. Let’s go ahead and compile the other trigger.

create or replace trigger logoff_t
before 
logoff
on database
begin
insert into log_table values (user,sysdate,'logoff');
end;

We have created the trigger with the name logoff_t and the event is logoff and the timing is before. The logic is if a user logged off from the database, then the username and system date, and “logoff” will be stored in the table log_table before the user logged off. Let’s go ahead and compile the trigger.

The trigger is now compiled successfully.

Step 4: Test the trigger

Let us go ahead and check the table log_table and see if there is any data present in it.

select user_id,to_char(log_date,’dd-mm-yyyy hh:mi:ss’), action from log_table;

So, there is no record present in the table log_table. Now, let’s go ahead and open the command prompt and connect to the database jayapdb.

Command: sqlplus hr/hr@jayapdb

So, we have logged into the database jayapdb. Let’s run the query of the table log_table and see if the record is inserted or not.

select user_id,to_char(log_date,’dd-mm-yyyy hh:mi:ss’), action from log_table;

So, the record is inserted in the table log_table. Let’s go ahead and exit from the database and see if the record is inserted while exiting from the database.

So, we have exited from the database. Let’s check the table log_table.

select user_id,to_char(log_date,’dd-mm-yyyy hh:mi:ss’), action from log_table;

So, the record is inserted again. This is how in real life we track the login and logoff of a user. We can drop the trigger using the below statements.

Drop trigger logon_t;
Drop trigger logoff_t;

More About Oracle Triggers

Call Statement in Triggers:

Now, we will learn about the call statement in triggers. We can create and replace a trigger. Let’s take an example trigger.

Create or replace trigger sample_trigger
Timing
Event
On table_name
[REFERENCING OLD AS old | NEW as new]
[FOR EACH ROW]
[WHEN CONDITION]
CALL procedure_name
/

This is the trigger example. Generally, we will mention the trigger logic by mentioning begin and end. Here we can use that in a procedure. Let us go ahead and create a procedure by executing the below statement.

Create or replace procedure log_execution is
Begin
 Dbms_output.put_line(‘log_execution, employee inserted’);
End;

So, the procedure is created. Let’s use this procedure in the trigger instead of logic.

Create or replace trigger log_employee
Before insert on employees
Call log_execution  -- no semicolon required.
/

We use these procedures in the trigger whenever there is a huge code and we cannot mention each and everything inside the trigger. So, we can use the procedures to write the code and then use the procedures inside the triggers.

Benefits of Event Triggers:

Improved Data Security:

• Provide enhanced and complex security checks
• Provide enhanced and complex auditing.

Improved Data Integrity:

• Enforce dynamic data integrity constraints
• Enforce complex referential integrity constraints
• Ensure that related operations are performed together implicitly.

System Privileges Required to Manage Triggers:

The following system privileges are required to manage triggers:

• The CREATE / ALTER /DROP (ANY) trigger privilege enables you to create a trigger in any schema
• The ADMINISTER DATABASE TRIGGER privilege that enables you to create a trigger on DATABASE. This privilege is only allowed by DBA. The DBA doesn’t provide this privilege to the developer.
• The EXECUTE privilege (if your trigger refers to any objects that are not in your schema).

Guidelines for Designing Triggers:

We can design triggers to perform related actions and centralize global operations. We should not design triggers where functionality is already built into the Oracle server. We should not create duplicate triggers

We can create stored procedures and invoke them in a trigger if the PL/SQL code is very lengthy.

Excessive use of triggers can result in complex interdependencies, which may be difficult to maintain in large applications.

Note: In general, we should create a large trigger that may lead to interdependencies.

Here, in this article, I try to explain System Event Triggers in Oracle with Examples. I hope you enjoy this System Event Triggers in Oracle with Examples article.