System triggers

System triggers can be classified into two types based on the triggering events: DDL triggers and database triggers. A DDL trigger is a trigger that is executed when a DDL statement is executed. A database trigger is a trigger that is executed when a database event occurs. Logon and logoff are database events, so these two types of triggers are database triggers. They are executed after a user logs in and before a user logs off, respectively.

Syntax of system triggers

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{LOGON | LOGOFF}
ON {[schema_name.]SCHEMA | DATABASE}
[{ FOLLOWS | PRECEDES } other_trigger_name]
[WHEN condition]
[ENABLE | DISABLE]
trigger_body;

Rules for using system triggers

• Only after logon and before logoff triggers can be created. before logon and after logoff triggers cannot be created.
• System triggers cannot be renamed.
• Triggers cannot be created on the SYS SCHEMA.
• If both SCHEMA and DATABASE level triggers are present during user login or logout, the SCHEMA level triggers are executed first, followed by the DATABASE level triggers.
• System triggers can be created on schemas (users) and databases. They are used to specify triggers for user login and all user logins, respectively. You can use ON user_name.SCHEMA to specify a trigger for a specific user login. If user_name is not specified, the trigger is executed when the user who created the trigger logs in.
• Each system trigger independently starts a transaction and commits it.
• If a logon trigger fails, the login fails. If a logoff trigger fails, the logout does not fail.
• If the user logging in is the owner of the trigger or has the ALTER ANY TRIGGER privilege, the login succeeds even if the trigger fails.

Examples

obclient> CREATE TABLE logon_logoff_log
(user_name varchar2(100), action varchar2(10), time timestamp);
Query OK, 0 rows affected (0.132 sec)

-- Schema-level logon trigger

obclient> CREATE or replace after_logon_trg after logon on user1.schema
begin
    insert into logon_logoff_log values ('user1', 'logon', systimestamp);
end;
/

-- Schema-level logoff trigger

obclient> CREATE or replace before_logoff_trg before logoff on user1.schema
begin
    insert into logon_logoff_log values ('user1', 'logoff', systimestamp);
end;
/

-- Database-level logon trigger

obclient> CREATE or replace after_logon_db_trg after logon on database
when (ora_login_user() in ('user1', 'user2', 'user3'))
begin
    insert into logon_logoff_log values (ora_login_user(), 'logon', systimestamp);
end;
/

-- Database-level logoff trigger

obclient> CREATE or replace before_logoff_db_trg before logoff on database
when (ora_login_user() in ('user1', 'user2', 'user3'))
begin
    insert into logon_logoff_log values (ora_login_user(), 'logoff', systimestamp);
end;
/