联系：手机/微信(+86 17813235971) QQ(107644445)
标题：触发器记录用户登录信息
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
1、先需要建立一张表，用于存放登陆信息
create table LOG$INFORMATION ( 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 )
2、创建触发器，记载登录信息
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 log$information(username,logintime,terminal,ipadress,osuser,machine,
program,sid,serial#,ausid)
VALUES(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;
3、用户登出触发器，记录登出时间
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 LOG$INFORMATION SET LOGOUT_TIME=SYSDATE WHERE sid=mtSession.Sid AND serial#=mtSession.Serial#;
END IF;
CLOSE cSession;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
