系统触发器

CREATE [OR REPLACE] TRIGGER 触发器名称    [BEFORE | AFTER] [数据库事件] ON [DATABASE | SCHEMA]    [WHEN 触发条件]    [DECLARE]        [程序声明部分 ;]    BEGIN        程序代码部分 ;    END [触发器名称] ;

--创建序列CREATE SEQUENCE user_log_seq;--创建日志表CREATE TABLE user_log(             logid          NUMBER CONSTRAINT  pk_logoid PRIMARY KEY,             username       VARCHAR2(50)           NOT NULL,             logodate       DATE,             logoffdate     DATE,             ip             VARCHAR2(20),             logtype        VARCHAR2(20));

--创建登录触发器create or replace trigger logon_trigger  AFTER LOGON ON DATABASE  declare begin   INSERT INTO user_log(logid,username,logodate,ip,logtype)  VALUES(user_log_seq.nextval,ora_login_user,SYSDATE,ora_client_ip_address,‘LOGON‘);end logon_trigger;

--创建登出触发器CREATE OR REPLACE TRIGGER LOGON_TRIGGER  BEFORE LOGOFF ON DATABASEDECLAREBEGIN  INSERT INTO USER_LOG    (LOGID, USERNAME, LOGOFFDATE, IP, LOGTYPE)  VALUES    (USER_LOG_SEQ.NEXTVAL,     ORA_LOGIN_USER,     SYSDATE,     ORA_CLIENT_IP_ADDRESS,     ‘LOGFF‘);END LOGON_TRIGGER;

SELECT * FROM user_log;