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 10g Enterprise 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@]