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.