logon on database记录登入信息的trigger

今天想起写一个登陆数据库的数据库级别的trigger，记录每次登陆的时间和人员名称，以下是具体的构建思路。

SQL> create table loggon01(id number,name varchar2(80),logon_date date);

Sql> create public synonym loggon for loggon01;

Sql> grant all on logon to public;

创建测试表loggon01及其public synonym授权与public

Sql> create sequence sequence01

Start with 1

Increment by 1

Maxvalue 99999

Nocycle

Sql> create public synonym sequence for sequence01;

Sql> grant select any sequence to public;

创建序列及其public synonym并授权与public

Sql> create or replace trigger trigger01

After logon on database /*如果用schema替换database，trigger只会记录本用户的记录信息。*/

Begin

Insert into logon values(sequence.nextval,user,sysdate);

End;

创建logon on database触发器，记录每次登陆人员信息。

SQL> delete from loggon;

28 rows deleted

SQL> disconnect;

Not logged on

清空数据，并logoff database。

SQL> conn xiaoyu/xiaoyu;

Connected to Oracle Database 10gEnterprise Edition Release 10.2.0.1.0

Connected as xiaoyu

SQL> select * from loggon;

ID NAME LOGG_DATE

---------- -------------------- -----------

54 XIAOYU 2012-1-18 1

57 XIAOYU 2012-1-18 1

55 XIAOYU 2012-1-18 1

56 DBSNMP 2012-1-18 1

从上述可以得出sequence也可以授予同义词，实现某种算法应该用批量更新数据一样，分批算法从而简化思路逐步完成，应该也是所谓细化算法，逐步实现。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25362835/viewspace-1057165/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25362835/viewspace-1057165/