----- 删除表 ---
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;
来源: http://www.bubuko.com/infodetail-2699664.html