oracle用户登入登出日志,oracle通过触发器实现登录登出日志记录

-----删除表--- drop table userlogininfo --创建登录登出记录信息表 create table userlogininfo ( infoid int primary key not null, USERNAME VARCHAR2(30), TERMINAL VARCHAR2(50), IPADRESS VARCHAR2(20), OSUSER VARCHAR2(30), MACHINE VARCHAR2(64), PROGRAM VARCHAR2(64), SID NUMBER, SERIAL# NUMBER, AUSID NUMBER, LOGINTIME DATE default sysdate, LOGout_TIME date ) ---删除序列--- DROP SEQUENCE seq_userlogininfo ---创建自动增长序列-- CREATE SEQUENCE seq_userlogininfo INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数 NOMAXVALUE -- 不设置最大值 NOCYCLE -- 一直累加，不循环 CACHE 10; ------- -----删除触发器---- DROP TRIGGER TR_LOGIN_RECORD ------创建登录触发器--- CREATE OR REPLACE TRIGGER TR_LOGIN_RECORD AFTER logon ON DATABASE DECLARE mtSession v$session%ROWTYPE; CURSOR cSession(iiQuerySid IN NUMBER) IS SELECT * FROM v$session WHERE nvl(osuser,‘x‘) <> ‘SYSTEM‘ and type <> ‘BACKGROUND‘ and audsid = iiQuerySid; BEGIN OPEN cSession(userenv(‘SESSIONID‘)); FETCH cSession INTO mtSession; IF cSession%FOUND THEN INSERT 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 or replace trigger TR_LOGOFF_RECORD before LOGOFF ON DATABASE DECLARE mtSession v$session%ROWTYPE; CURSOR cSession(iiQuerySid IN NUMBER) IS SELECT * FROM v$session where nvl(osuser,‘x‘) <> ‘SYSTEM‘ and type <> ‘BACKGROUND‘ and audsid = iiQuerySid; BEGIN OPEN 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 THEN raise; END;