Oracle PL/SQL进阶编程（第十三弹：系统事件触发器）

DML触发器和替代触发器都是在DML事件上触发的 ，而系统触发器是在DDL事件和数据库服务器事件时触发的。

定义系统触发器

语法如下：

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} {DDL event |DATABASE event}
ON {DATABASE | SCHEMA}
[WHEN [...]]
plsql_block

DDL event包括ALTER、ANALYZE、ASSOCIATE STATISTICS、AUDIT、COMMENT、CREATE、DROP、GRANT、RENAME、REVOKE、TRUNCATE等。
DATABASE event是数据库级的系统事件，对每一个触发的事件，Oracle会打开一个匿名事务，触发触发器，提交任何独立的事务，这些事件有SERVERERROR、LOGON、LOGOFF、STARTUP、SHUTDOWN、SUSPEND。

在创建数据库级的触发器时，必须具有ADMINISTER DATABASE TRIGGER的系统权限。

代码如下：

CREATE TABLE created_log
(
    obj_owner VARCHAR2(30),   --所有者
    obj_name  VARCHAR2(30),   --对象名称
    obj_type  VARCHAR2(20),   --对象类型
    obj_user VARCHAR2(30),    --创建用户
    created_date DATE     --创建日期
)

CREATE OR REPLACE TRIGGER t_created_log
   AFTER CREATE ON scott.SCHEMA                  --在soctt方案下创建对象后触发
BEGIN
   --插入日志记录
   INSERT INTO scott.created_log(obj_owner, obj_name,
                obj_type, obj_user, created_date
               )
        VALUES (SYS.dictionary_obj_owner, SYS.dictionary_obj_name,
                SYS.dictionary_obj_type, SYS.login_user, SYSDATE
               );
END;

触发器事件列表

DDL触发器事件列表如下：

事件	触发时机	描述
CRAETE	BEFORE/AFTER	在创建一个方案对象之前或之后触发，比如创建表、索引等对象
DROP	BEFORE/AFTER	在删除一个方案对象之前或之后触发
ALTER	BEFORE/AFTER	在修改一个方案对象之前或之后触发
ANALYZE	BEFORE/AFTER	在使用ANALYZE分析数据库对象之前或之后触发
ASSOCIATE STATISTICS	BEFORE/AFTER	统计相关的数据库对象之前或之后触发
AUDIT	BEFORE/AFTER	在使用AUDIT开启审计功能之前或之后触发
COMMENT	BEFORE/AFTER	在对一个数据库对象应用注释之前或之后触发
DISASSOCIATE STATISTICS	BEFORE/AFTER	取消对一个数据库对象的统计之前或之后触发
GRANT	BEFORE/AFTER	在使用GRANT分配权限之前或之后触发
NOAUDIT	BEFORE/AFTER	在使用NOAUDIT语句关闭审计功能之前或之后触发
RENAME	BEFORE/AFTER	在使用RENAME对一个数据库对象进行重命名之前或之后触发
REVOKE	BEFORE/AFTER	在使用REVOKE语句取消权限之前或之后触发
TRUNCATE	BEFORE/AFTER	在使用TRUNCATE清除一个表的内容之前或之后触发
DDL	BEFORE/AFTER	DDL指定在本表格中列出的任何事件执行之前或之后触发

数据库系统触发器事件列表如下：

事件	触发时机	描述
STARTUP	AFTER	当数据库实例启动后触发
SHUTDOWN	BEFORE	当数据库实例关闭前触发，如果数据库是异常关闭，那么这个时间可能不会触发
SERVERERROR	AFTER	只要发生错误就会被触发
LOGON	AFTER	当一个用户成功连接到该数据库后触发
LOGOFF	BEFORE	当用户注销前触发

来看一段代码：

--以DBA身份登录，创建下面的登录记录表
CREATE TABLE log_db_table
(
   username VARCHAR2(20),
   logon_time DATE,
   logoff_time DATE,
   address VARCHAR2(20) 
);

--以DBA身份登录，创建DATABASE级别的LOGON事件触发器
CREATE OR REPLACE TRIGGER t_db_logon
AFTER LOGON ON DATABASE
BEGIN
  INSERT INTO log_db_table(username,logon_time,address)
              VALUES(ora_login_user,SYSDATE,ora_client_ip_address);
END;

触发器属性列表

Oracle在DBMS_STANDARD包中提供了一些功能性的函数，以便在开发系统级别的触发器时可以提供一些系统级别的信息。

属性函数	描述
ora_client_ip_address	返回客户端的IP地址
ora_database_name	返回当前数据库的名称
ora_des_encrypted_password	返回DES加密后的用户口令
ora_dict_obj_name	返回DDL操作所对应的数据库对象名
ora_dict_obj_name_list(name_list OUT ora_name_list_t)	返回在事件中被修改的对象名列表
ora_dict_obj_owner	返回DDL操作所对应的对象的所有者名称
ora_dict_obj_owner_list(owner_list OUT ora_name_list_t)	返回在事件中被修改的对象的所有者列表
ora_dict_obj_type	返回DDL操作所对应的数据库对象的类型
ora_grantee(user_list OUT ora_name_list_t)	返回授权事件的授权者
ora_instance_num	返回例程号
ora_is_alter_column(column_name IN VARCHAR2)	检测特定列是否被修改
ora_is_creating_nested_table	用于检测是否正在建立嵌套表
ora_is_servererroe(error_number)	用于检测是否返回了特定Oracle错误
ora_login_user	用于范湖登录用户名
ora_sysevent	用户返回触发触发器的系统事件名
ora_is_drop_column	如果指定的columnname正在被移除，则返回True，否则返回False
ora_is_alter_column	如果指定的columnname已经被修改，则返回True，否则返回False

这里的ora_name_list_t是定义在DBMS_STANDARD包中的一个嵌套表类型，定义如下：
TYPE ora_name_list_t IS TABLE OF VARCHAR2(64);

属性函数使用示例

如下代码演示了在数据库启动和关闭时，使用ora_sysevent来获取系统事件的名称：

--以DBA身份进入系统，创建临时表
CREATE TABLE event_table(
   sys_event VARCHAR2(30),
   event_time DATE
);
--在DBA级别创建如下的2个触发器
CREATE OR REPLACE TRIGGER t_startup
AFTER STARTUP ON DATABASE       --STARTUP只能是AFTER
BEGIN
   INSERT INTO event_table VALUES(ora_sysevent,SYSDATE);
END;
/
CREATE OR REPLACE TRIGGER t_startup
BEFORE SHUTDOWN ON DATABASE       --SHUTDOWN只能是BEFORE
BEGIN
   INSERT INTO event_table VALUES(ora_sysevent,SYSDATE);
END;
/

下面的代码演示了如何使用ora_is_alter_column来防止用户对emp表的empno字段进行修改，用ora_is_drop_column来防止emp表的empno字段被删除：

CREATE OR REPLACE TRIGGER preserve_app_cols
   AFTER ALTER ON SCHEMA
DECLARE
   --获取一个表中所有列的游标
   CURSOR curs_get_columns (cp_owner VARCHAR2, cp_table VARCHAR2)
   IS
      SELECT column_name
        FROM all_tab_columns
       WHERE owner = cp_owner AND table_name = cp_table;
BEGIN
   -- 如果正使用的是ALTER TABLE语句修改表
   IF ora_dict_obj_type = 'TABLE'
   THEN
      -- 循环表中的每一列
      FOR v_column_rec IN curs_get_columns (
                             ora_dict_obj_owner,
                             ora_dict_obj_name
                          )
      LOOP
         --判断当前的列名正在被修改
         IF ORA_IS_ALTER_COLUMN (v_column_rec.column_name) THEN
            IF v_column_rec.column_name='EMPNO' THEN
               RAISE_APPLICATION_ERROR (-20003, '不能对empno字段进行修改');
            END IF; 
         END IF; 
         IF ORA_IS_DROP_COLUMN('EMPNO') THEN
            RAISE_APPLICATION_ERROR (-20004, '不能对empno字段进行删除');
         END IF; 
      END LOOP;
   END IF;
END;

定义SERVERERROR触发器

SERVERERROR事件可以用来跟踪数据库中发生的错误，错误代码可以通过SERVER_ERROR属性函数在触发器内部得到，可以通过该函数确定堆栈中的错误代码，可以使用DBMS_UTILITY.FORMAT_ERROR_STACK获取错误信息。
使用AFTER SERVERERROR时必须要了解如下的错误是否会被触发：
- ORA-00600：Oracle内部错误
- ORA-01034：Oracle不可用
- ORA-01403：没有找到数据
- ORA-01422：提取操作返回大于请求的行数
- ORA-01423：在一个提取操作中检测到额外的行
- ORA-04030：在分配字节时内存不够

AFTER SERVERERROR触发器并没有提供方法来调整出现的错误，仅能包含错误的相关的信息，管理员可以通过使用这些触发器来构建强大的日志机制。

如下代码演示了如何通过创建AFTER SERVERERROR触发器来记录数据库服务器发生的各种错误：

--错误日志记录表
CREATE TABLE servererror_log(
   error_time DATE,
   username  VARCHAR2(30),
   instance NUMBER,
   db_name VARCHAR2(50),
   error_stack VARCHAR2(2000)
);
--创建错误触发器，在出现数据库错误时触发。
CREATE OR REPLACE TRIGGER t_logerrors
   AFTER SERVERERROR ON DATABASE
BEGIN
   INSERT INTO servererror_log
        VALUES (SYSDATE, login_user, instance_num, database_name,
                DBMS_UTILITY.format_error_stack);
END;