Oracle trigger 4-database event trigger

Source: Internet
Author: User

Oracle trigger 4-database event trigger Oracle trigger 3-DDL trigger http://www.bkjia.com/database/201304/201421.html Create database event TRIGGER Syntax 1 CREATE [or replace] TRIGGER trigger_name2 {BEFORE | AFTER} {database_event} ON {DATABASE | SCHEMA} 3 DECLARE4 Variable declarations5 BEGIN6... some code... 7. The database event trigger is triggered when a database event occurs. There are 6 database event triggers. STARTUP is triggered when the database is opened. No before startup trigger. Example: create or replace trigger startup_pinnerAFTER startup on DATABASEBEGINpin_plsql_packages; pin_application_packages; END; triggered when the SHUTDOWN database is shut down normally. There is no after shutdown trigger. Example: create or replace trigger before_shutdownBEFORE shutdown on DATABASEBEGINgather_system_stats; END; Note: shutdown nomal OR shutdown immediate cannot be triggered unless shutdown nomal or shutdown immediate is disabled normally. SERVERERROR is 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_loggerAFTER SERVERERRORON SCHEMADECLAREv_errnum NUMBER; -- the Oracle error # v_now DATE: = SYSDATE; -- current timeBEGIN -- for every error in the error stack... FOR e_counter I N 1 .. ORA_SERVER_ERROR_DEPTH LOOP -- write the error out to the log table; no -- commit is required because we are in an -- autonomous transactionINSERT 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 stackEND;/LOGON is triggered when a database session starts. No before logon trigger. Example: create or replace trigger after_logonAFTER logon on SCHEMADECLAREv_ SQL VARCHAR2 (100 ): = 'alter session enable resumable' | 'timeout 10 name' | ''' | 'olap session' | '''; beginexecute immediate v_ SQL; DBMS_SESSION.SET_CONTEXT ('olap Namespace ', 'customer id', load_user_customer_id); END; LOGOFF is triggered when a database session ends normally. No after logoff trigger. Example: create or replace trigger before_logoffBEFORE logoff on DATABASEBEGINgather_session_stats; END; DB_ROLE_CHANGE is triggered when a standby database is switched to the primary database OR vice versa. It is mostly used for logging uard.

Related Article

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.