Trigger VI (System trigger) (Learning Note)

system triggers

System triggers are used to monitor the access of database services for information such as open, close, error, or to monitor user behavior. If you are creating a system trigger, you can use the following syntax

CREATE [OR REPLACE] TRIGGERTrigger Name[before | after] [Database Events]  on [DATABASE | SCHEMA]    [When trigger condition]    [DECLARE]        [part of the program Declaration;]    BEGINpart of the program code; END [Trigger Name];

use an administrator to create a system trigger System trigger Events

NO.	Event	Trigger Time	Describe
1	STARTUP	After	Triggers after the DB instance is started
2	SHUTDOWN	Before	Triggered before the DB instance is closed
3	Servererror	After	Triggered when an error occurs
4	LOGON	After	Triggered after a user logs on
5	LOGOFF	BEFOR	Trigger before user logoff

Example one , login log out logging function

--Create a sequenceCREATESEQUENCE user_log_seq;--Create a log tableCREATE TABLEUser_log (Logid Number CONSTRAINTPk_logoidPRIMARY KEY, usernameVARCHAR2( -) not NULL, logodate date, logoffdate date, IPVARCHAR2( -), LogTypeVARCHAR2( -));

 --  Create a login trigger   or  replace  trigger   Logon_trigger after logon  on  database  declare  begin  insert  into   User_log (logid,username,logodate,ip,logtype)  values  (User_log_ Seq.nextval,ora_login_user,sysdate,ora_client_ip_address, logon   " );  end  Logon_trigger; 
 

--Create a logout triggerCREATE OR REPLACE TRIGGERLogon_trigger before LOGOFF on DATABASEDECLAREBEGIN  INSERT  intoUser_log (Logid, USERNAME, Logoffdate, IP, LogType)VALUES(User_log_seq. Nextval, Ora_login_user, Sysdate, ora_client_ip_address,'LOGFF');ENDLogon_trigger;

Switch users to sign in

Use admin login to view the User_log table

SELECT *  from User_log;

Trigger VI (System trigger) (Learning Note)