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;