Oracle12C--系统触发器(58)

• 系统触发器定义
  • 定义：系统触发器用户监视数据库服务的打开，关闭，错误等信息的取得，或者监控用户的行为操作等；
  • 语法：

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

• 系统触发器事件：
  • 

• 常用示例
  • 登录日志功能（此示例应使用sys管理员账号登录）：创建一个监控数据打开及关闭的触发器，当管理员每次执行数据库实例的打开及关闭操作时，可以在一个database_log数据表中保存操作信息；
    • 步骤1：编写user_log数据表创建脚本

DROP SEQUENCE user_log_seq ; DROP TABLE user_log PURGE ; CREATE SEQUENCE user_log_seq ; CREATE TABLE user_log ( logid NUMBER CONSTRAINT pk_logid PRIMARY KEY , username VARCHAR2(50) NOT NULL , logondate DATE , logoffdate DATE , ip VARCHAR2(20) , logtype VARCHAR2(20) ) ;

• 步骤2：监控用户登录触发器 —— logon_trigger

CREATE OR REPLACE TRIGGER logon_trigger AFTER LOGON ON DATABASE BEGIN INSERT INTO user_log(logid,username,logondate,ip,logtype) VALUES (user_log_seq.nextval,ORA_LOGIN_USER,SYSDATE,ORA_CLIENT_IP_ADDRESS,'LOGON') ; END ; /

• 步骤3：监控用户注销触发器 —— logoff_trigger

CREATE OR REPLACE TRIGGER logoff_trigger BEFORE LOGOFF ON DATABASE BEGIN INSERT INTO user_log(logid,username,logoffdate,ip,logtype) VALUES (user_log_seq.nextval,ORA_LOGIN_USER,SYSDATE,ORA_CLIENT_IP_ADDRESS,'LOGOFF') ; END ; /

• 效果：以上步骤完成之后，分别使用几个用户执行登录操作，然后回到sys用户下，查询user_log数据表
  • 回到sys用户下，查询user_log数据表

CONN sys/change_on_install AS SYSDBA ; SELECT * FROM user_log ;

• 注意：上面的登录触发器和注销触发器是不能合并为一个触发器的，因为他们的触发时机不一样。登录的触发时机只能使用AFTER，注销的触发时机只能使用BEFORE；

• 系统启动和关闭日志功能：该程序的主要功能是在一张数据库事件表（db_event_log）中记录数据库实例启动和关闭的日期时间；
  • 步骤1：定义数据库事件记录表

DROP TABLE db_event_log PURGE ; DROP SEQUENCE db_event_log_seq ; CREATE SEQUENCE db_event_log_seq ; CREATE TABLE db_event_log ( eventId NUMBER CONSTRAINT pk_eventid PRIMARY KEY , eventType VARCHAR2(50) NOT NULL , eventDate DATE NOT NULL , eventUser VARCHAR2(50) NOT NULL ) ;

• 步骤2：编写触发器 —— 启动之后触发

CREATE OR REPLACE TRIGGER startup_trigger AFTER STARTUP ON DATABASE BEGIN INSERT INTO db_event_log(eventId,eventType,eventDate,eventUser) VALUES (db_event_log_seq.nextval,'STARTUP',SYSDATE,ORA_LOGIN_USER) ; COMMIT ; END ; /

• 步骤3：编写触发器 —— 关闭之前触发

CREATE OR REPLACE TRIGGER shutdown_trigger BEFORE SHUTDOWN ON DATABASE BEGIN INSERT INTO db_event_log(eventId,eventType,eventDate,eventUser) VALUES (db_event_log_seq.nextval,'SHUTDOWN',SYSDATE,ORA_LOGIN_USER) ; COMMIT ; END ; /

• 步骤4：创建完毕之后，使用sys用户，依次执行数据库实例关闭与打开两个操作，然后查询db_event_log数据表，观察记录结果
• 步骤5：查询db_event_log数据表记录

SHUTDOWN ABORT STARTUP SELECT * FROM db_event_log ;

• 错误信息日志：数据库开发会出现许多错误信息，这些错误信息分析问题非常重要。可以利用SERVERERROR对所出现的错误进行触发。对错误信息继续触发操作时，可使用一个DBMS_UTILITY.FORMAT_ERROR_STACK来获得错误堆栈的信息。用示例来说明问题；
  • 步骤1：创建一张记录错误信息的数据表 —— db_error

DROP SEQUENCE db_error_seq ; DROP TABLE db_error PURGE ; CREATE SEQUENCE db_error_seq ; CREATE TABLE db_error ( eid NUMBER CONSTRAINT pk_eid PRIMARY KEY , username VARCHAR2(50) , errorDate DATE , dbname VARCHAR2(50) , content CLOB ) ;

• 步骤2：定义数据库错误触发器

CREATE OR REPLACE TRIGGER error_trigger AFTER SERVERERROR ON DATABASE 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) ; END ; /

• 触发器定义完成，可在c##scott用户下分别执行两个错误的操作，然后观察db_error表中记录的错误信息；
• 步骤3：定义错误的DML操作：
  • 
  • 查询错误信息：

select * from db_error;