Oracle login log Logging via triggers

Tag:ddr   min    Auto    upd   when    rop   suse   and   primary   

-----Delete Table---drop table userlogininfo--Create login log out record information table userlogininfo (infoid int primary key NOT NULL, USERNAME Varc HAR2 (+), TERMINAL VARCHAR2 (+), ipadress VARCHAR2, Osuser VARCHAR2 (+), machine VARCHAR2 (+), program VARCHAR2 (64), SID number, serial# number, ausid number, Logintime date default sysdate, Logout_time date)---Delete sequence---drop SEQUENCE seq_use Rlogininfo---Create an auto-grow sequence--create SEQUENCE seq_userlogininfo INCREMENT by 1--add a few start with 1 at a time-counting from 1 nomaxvalue-not setting the most Large value nocycle--always accumulate, do not cycle CACHE 10; ------------Delete trigger----drop TRIGGER tr_login_record------Create a logon trigger---a Create OR REPLACE TRIGGER tr_login_recordafter logon On Databasedeclaremtsession V$session%rowtype; CURSOR CSession (Iiquerysid in number) is a SELECT * from V$session WHERE NVL (Osuser, ' x ') <> ' SYSTEM ' and type <> ' BACKGROUND ' and audsid = Iiquerysid; Beginopen CSession (Userenv (' SESSIONID ')); FETCH csession into mtsession; IF Csession%found Theninsert into Userlogininfo (infoid,username,logintime,terminal,IPADRESS,OSUSER,MACHINE,PROGRAM,SID,SERIAL#,AUSID) VALUES (Seq_userlogininfo.nextval,user,sysdate, Mtsession.terminal, Sys_context (' USERENV ', ' ip_address '), Mtsession.osuser, Mtsession.machine,mtsession.program, Mtsession.sid,mtsession.serial#,userenv (' SESSIONID ')); END IF; CLOSE CSession; EXCEPTION when OTHERS then raise;end;-----Create logout update logout time trigger---Create or replace trigger Tr_logoff_recordbefore LOGOFF on DATA Basedeclaremtsession V$session%rowtype; CURSOR CSession (Iiquerysid in number) is a SELECT * from V$session where NVL (Osuser, ' x ') <> ' SYSTEM ' and type <> ' BACKGROUND ' and audsid = Iiquerysid; Beginopen CSession (Userenv (' SESSIONID ')); FETCH csession into mtsession; IF Csession%found then UPDATE userlogininfo SET logout_time=sysdate WHERE sid=mtsession.sid and Serial#=mtsession.serial #; END IF; CLOSE CSession; EXCEPTION when OTHERS and then raise; END;

Oracle Login log-out logging via triggers