ORACLE trigger 4-database event trigger

Create Database event trigger syntax

1 Create [or replace] trigger trigger_name
2 {before | after} {database_event} on {database | schema}

3 declare
4 variable declarations
5 begin
6... some code...
7 end;

Database event triggers are triggered when a database event occurs. There are 6 database event triggers. Startup

Triggered when the database is opened.

No before startup trigger.

Example:

Create or replace trigger startup_pinner
After startup on Database
Begin
Pin_plsql_packages;
Pin_application_packages;
End;

Shutdown

Triggered when the database is shut down normally.

There is no after Shutdown trigger.

Example:

Create or replace trigger before_shutdown
Before shutdown on Database
Begin
Gather_system_stats;
End;

Note: shutdown abort is triggered only when shutdown nomal or shutdown immediate is disabled normally. Otherwise, shutdown abort cannot be triggered.

Servererror

Triggered when a database error occurs.

No before servererror trigger.

Example:

Drop trigger error_logger;

Drop table error_log;

Create sequence error_seq;

Create Table error_log
(Error_id number,
Username varchar2 (30 ),
Error_number number,
Sequence number,
Timestamp date );

Create or replace trigger error_logger
After servererror
On Schema
Declare

V_errnum number; -- the Oracle Error #
V_now Date: = sysdate; -- current time

Begin

-- For every error in the error stack...
For e_counter in 1 .. ora_server_error_depth Loop

-- Write the error out to the log table; no
-- Commit is required because we are in
-- Autonomous transaction
Insert into error_log (error_id,
Username,
Error_number,
Sequence,
Timestamp)
Values (error_seq.nextval,
User,
Ora_server_error (e_counter ),
E_counter,
V_now );

End loop; -- every error on the stack

End;
/

 

Logon

Triggered when a database session starts.

No before logon trigger.

Example:

Create or replace trigger after_logon
After logon on Schema
Declare
V_ SQL varchar2 (100): = 'alter session enable resumable' |
'Timeout 10 name' | ''' |
'Olap session '| '''';

Begin
Execute immediate v_ SQL;
Dbms_session.set_context ('olap namespace ',
'Customer id ',
Load_user_customer_id );
End;

 

Logoff

Triggered when a database session ends normally.

No after logoff trigger.

Example:

Create or replace trigger before_logoff
Before logoff on Database
Begin
Gather_session_stats;
End;

Db_role_change

Triggered when a standby database is switched to the primary database or vice versa.

It is mostly used for logging uard.