ORACLE trigger 4-database event trigger

Source: Internet
Author: User
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.