系统触发器
系统触发器用于监视数据库服务的打开、关闭、错误等信息的取得，或者是监控用户的行为操作等。如果要创建系统触发器，可以使用如下的语法
CREATE [OR REPLACE] TRIGGER 触发器名称 [BEFORE | AFTER] [数据库事件] ON [DATABASE | SCHEMA] [WHEN 触发条件] [DECLARE] [程序声明部分 ;] BEGIN 程序代码部分 ; END [触发器名称] ;
使用管理员才可以创建系统触发器
系统触发器事件
| NO. | 事件 | 触发时机 | 描述 |
| 1 | STARTUP | AFTER | 数据库实例启动之后触发 |
| 2 | SHUTDOWN | BEFORE | 数据库实例关闭之前触发 |
| 3 | SERVERERROR | AFTER | 出错误时触发 |
| 4 | LOGON | AFTER | 用户登录后触发 |
| 5 | LOGOFF | BEFOR | 用户注销前触发 |
示例一、登录登出日志功能
--创建序列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;
切换用户进行登录
使用管理登录，查看user_log表
SELECT * FROM user_log;
示例二、 系统启动和关闭时，日志记录功能
--创建索引CREATE SEQUENCE db_event_log_seq;--查询索引SELECT * FROM user_sequences WHERE sequence_name='DB_EVENT_LOG_SEQ';--创建数据库记录事件表CREATE TABLE db_event_log( eventid NUMBER CONSTRAINT pk_eventid PRIMARY KEY, enentType VARCHAR2(50) NOT NULL, enentDate DATE NOT NULL, eventUser VARCHAR2(50) NOT NULL);--查询表SELECT * FROM db_event_log;
--创建启动之后触发器create or replace trigger startup_trigger after startup ON DATABASE declare BEGIN INSERT INTO db_event_log(eventid,enenttype,enentdate,eventuser) VALUES(db_event_log_seq.nextval,'STARTUP',SYSDATE,ora_login_user); COMMIT;end startup_trigger;--创建关闭之前触发器create or replace trigger shutdown_trigger before shutdown ON DATABASE declare begin INSERT INTO db_event_log(eventid,enenttype,enentdate,eventuser) VALUES(db_event_log_seq.nextval,'SHUTDOWN',SYSDATE,ora_login_user); COMMIT;end shutdown_trigger;
-测试在sqlplus中执行SHUTDOWN ABORT; --立刻关闭startup --启动SELECT * FROM db_event_log;
示例三、 错误信息日志
--创建索引CREATE SEQUENCE db_error_seq;--查询索引SELECT * FROM user_sequences WHERE sequence_name='DB_ERROR_SEQ';---创建一张记录错误信息的数据表CREATE TABLE db_error( eid NUMBER CONSTRAINT pk_eid PRIMARY KEY, username VARCHAR2(50), errorDate DATE, dbname VARCHAR2(50), CONTENT CLOB);--查询表SELECT * FROM db_error;
--创建数据库错误触发器create or replace trigger error_trigger after servererror ON DATABASE declare begin INSERT INTO db_error(eid,username,errordate,dbname,content) VALUES(db_error_seq.nextval,ora_login_user,SYSDATE,ora_database_name,dbms_utility.format_error_stack); --ora_login_user 用户名 --ora_database_name 数据库名 --dbms_utility包 --dbms_utility.format_error_stack错误内容end error_trigger;
--测试--使用普通用户SELECT * FROM orcl;INSERT INTO dept(deptno,dname,loc)VALUES(10,'111','SZ');--查询表SELECT * FROM db_error;