oracle 触发器登录,oracle 利用触发器记要用户登录登出信息

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;

select *from LOG$INFORMATION;