System triggers

System triggers can be classified into two types based on the triggering events: DDL triggers and database triggers. DDL triggers are triggered by executing DDL operations, while database triggers are triggered by database events. Logon and logoff are database events, so these triggers are database triggers, which are triggered after a user logs in and before a user logs off.

Syntax

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;

System trigger rules

• 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 triggers exist during user login or logout, the SCHEMA triggers are executed first, followed by the DATABASE 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 starts a transaction independently and commits the transaction independently.
• 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 trigger owner 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;
/