Trigger 6 (system trigger) (Learning notes), system trigger learning notes

Trigger 6 (system trigger) (Learning notes), system trigger learning notes
System triggers

System triggers are used to monitor the acquisition of information such as opening, closing, and error of database services, or to monitor user behavior and operations. To create a system trigger, you can use the following syntax:

 

CREATE [or replace] TRIGGER name [BEFORE | AFTER] [DATABASE event] ON [DATABASE | SCHEMA] [when trigger condition] [DECLARE] [Program declaration part;] BEGIN program code; END [Trigger name];

 

The administrator can create a system trigger. System trigger event

NO.	Event	Trigger time	Description
1	STARTUP	AFTER	Triggered after the database instance is started
2	SHUTDOWN	BEFORE	Triggered before the database instance is closed
3	SERVERERROR	AFTER	Triggered when an error occurs.
4	LOGON	AFTER	Triggered after User Logon
5	LOGOFF	BEFOR	Triggered before cancellation

Example 1Log on and log off

-- Create sequence user_log_seq; -- CREATE a log table create table user_log (logid number constraint pk_logoid primary key, username VARCHAR2 (50) not null, logodate DATE, logoffdate DATE, ip VARCHAR2 (20), logtype VARCHAR2 (20 ));

-- Create a LOGON trigger create or replace trigger logon_trigger after logon on database declare begin insert into user_log (logid, username, logodate, ip, logtype) VALUES (VALUES, ora_login_user, SYSDATE, VALUES, 'logon'); end logon_trigger;

-- Create or replace trigger LOGON_TRIGGER before logoff on databasedeclarebegin insert into USER_LOG (LOGID, USERNAME, LOGOFFDATE, IP, LOGTYPE) VALUES (VALUES, ORA_LOGIN_USER, SYSDATE, VALUES, 'logff'); END LOGON_TRIGGER;

Switch the user to log on

Use Management logon to view the user_log table

SELECT * FROM user_log;

Example 2,Logging is enabled and disabled.

 

-- CREATE index create sequence db_event_log_seq; -- Query index SELECT * FROM user_sequences WHERE sequence_name = 'db _ EVENT_LOG_SEQ '; -- CREATE database record event table create table db_event_log, enentType VARCHAR2 (50) not null, enentDate date not null, eventUser VARCHAR2 (50) not null); -- Query table SELECT * FROM db_event_log;

-- Create or replace trigger startup_trigger after startup on database declare begin insert into db_event_log (eventid, enenttype, enentdate, eventuser) VALUES (VALUES, 'startup', SYSDATE, begin); COMMIT; end startup_trigger; -- create or replace trigger shutdown_trigger before shutdown on database declare begin insert into db_event_log (eventid, enenttype, enentdate, eventuser) VALUES (events, events, 'shutdown ', SYSDATE, ora_login_user); COMMIT; end shutdown_trigger;

-Execute shutdown abort in sqlplus; -- immediately disable startup -- start SELECT * FROM db_event_log;

Example 3,Error Message Log

-- Create sequence db_error_seq; -- Query index SELECT * FROM user_sequences WHERE sequence_name = 'db _ ERROR_SEQ '; --- create table db_error (eid number constraint pk_eid primary key, username VARCHAR2 (50), errorDate DATE, dbname VARCHAR2 (50), content clob ); -- query the table SELECT * FROM db_error;

-- Create DATABASE Error trigger create or replace trigger error_trigger after servererror on database declare begin insert into db_error, dbms_utility.format_error_stack); -- ora_login_user username -- ora_database_name database name -- dbms_utility package -- dbms_utility.format_error_stack error content end error_trigger;

-- Test -- use a common user SELECT * FROM orcl; insert into dept (deptno, dname, loc) VALUES (10, '2017010', 'sz '); -- query the table SELECT * FROM db_error;