oracle通过触发器实现登录登出日志记录
发布时间：2020-12-12 13:29:05 所属栏目：百科 来源：网络整理
导读：-----删除表--- 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),PROGRA
-----删除表--- 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;
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |