1 CREATE TABLE SYS.USER_LOGIN_CHECK
2 (
3 USERNAME VARCHAR2(40 BYTE),
4 IPADDR VARCHAR2(30 BYTE),
5 LAST_LOGIN_TIME TIMESTAMP(6)
6 );
7
8
9
10 DROP TRIGGER SYS.TEST;
11
12 CREATE OR REPLACE TRIGGER SYS.TEST
13 AFTER logon ON database
14 DECLARE
15 v_username sys.user_login_check.USERNAME%type;
16 v_ipaddr sys.user_login_check.IPADDR%type;
17 v_count number;
18 BEGIN
19 if sys_context('userenv', 'ip_address') is null then
20 select count(1) into v_count from sys.user_login_check where username=user and IPADDR is null;
21 else
22 select count(1) into v_count from sys.user_login_check where username=user and IPADDR=sys_context('userenv', 'ip_address');
23 end if;
24
25 if v_count > 0 then
26 update sys.user_login_check set LAST_LOGIN_TIME=sysdate where username = user and IPADDR=sys_context('userenv', 'ip_address');
27 else
28 insert into sys.user_login_check values(user,sys_context('userenv', 'ip_address'),sysdate);
29 end if;
30 commit;
31
32 END test;
33 /
