Oracle触发器3-DDL触发器
Oracle触发器2-DML触发器
http://www.2cto.com/database/201304/200549.html
DDL触发器，当执行DDL语句时会被触发。按照作用范围，分为schema triggers,database triggers。schema triggers作用在一个用户上，database triggers作用在整个
数据库所有用户上。
创建DDL触发器
要创建一个DDL触发器，语法如下：
1 CREATE [OR REPLACE] TRIGGER trigger name --创建一个触发器并制定名称，or replace是可选项
2 {BEFORE | AFTER } { DDL event} ON {DATABASE | SCHEMA} --指定触发器是在DDL事件之前、之后触发。范围是on database、on schema
3 [WHEN (...)] --可选的WHEN子句，使用逻辑判断来避免触发器无意义的执行
4 DECLARE --触发器具体内容4-7
5 Variable declarations
6 BEGIN
7 ...some code...
8 END;
Examples:
SQL> CREATE OR REPLACE TRIGGER hr.testtrigger
2 AFTER CREATE ON SCHEMA -- on schema 作用范围只是在hr用户下create table等触发，其他用户则不会。若是on database则其他用户create table时会触发该触发器
3 BEGIN
4 -- 以下使用的是事件属性
5 DBMS_OUTPUT.PUT_LINE('I believe you have created a ' ||
6 ORA_DICT_OBJ_TYPE || ' called ' ||
7 ORA_DICT_OBJ_NAME);
8 END;
9 /
Trigger created.
可用事件
可用的DDL事件
DDL事件
触发时机
ALTER
对数据库中的任何一个对象使用SQL的ALTER命令时触发
ANALYZE
对数据库中的任何一个对象使用SQL的ANALYZE命令时触发
ASSOCIATE STATISTICS
统计数据关联到数据库对象时触发
AUDIT
通过SQL的AUDIT命令打开审计时触发
COMMENT
对数据库对象做注释时触发
CREATE
通过SQL的CREATE命令创建数据库对象时触发
DDL
列表中所用的事件都会触发
DISASSOCIATE STATISTICS
去掉统计数据和数据库对象的关联时触发
DROP
通过SQL的DROP命令删除数据库对象时触发
GRANT
通过SQL的GRANT命令赋权时触发
NOAUDIT
通过SQL的NOAUDIT关闭审计时触发
RENAME
通过SQL的RENAME命令对对象重命名时触发
REVOKE
通过SQL的REVOKE语句撤销授权时触发
TRUNCATE
通过SQL的TRUNCATE语句截断表时触发
可用属性
Oracle 提供了一系列的函数用来提供关于什么触发了DDL触发器以及触发器的状态灯信息。上面那个触发器的例子就使用了属性。
DDL触发器事件以及属性函数
函数名
返回值
ORA_CLIENT_IP_ADDRESS
客户端IP地址
ORA_DATABASE_NAME
数据库名称
ORA_DES_ENCRYPTED_PASSWORD
当前用户的DES算法
加密后的密码
ORA_DICT_OBJ_NAME
触发DDL的数据库对象名称
ORA_DICT_OBJ_NAME_LIST
受影响的对象数量和名称列表
ORA_DICT_OBJ_OWNER
触发DDL的数据库对象属主
ORA_DICT_OBJ_OWNER_LIST
受影响的对象数量和名称列表
ORA_DICT_OBJ_TYPE
触发DDL的数据库对象类型
ORA_GRANTEE
被授权人数量
ORA_INSTANCE_NUM
数据库实例数量
ORA_IS_ALTER_COLUMN
如果操作的参数column_name指定的列，返回true,否则false
ORA_IS_CREATING_NESTED_TABLE
如果正在创建一个嵌套表则返回true,否则false
ORA_IS_DROP_COLUMN
如果删除的参数column_name指定的列，返回true,否则false
ORA_LOGIN_USER
触发器所在的用户名
ORA_PARTITION_POS
SQL命令中可以正确添加分区子句位置
ORA_PRIVILEGE_LIST
授予或者回收的权限的数量。
ORA_REVOKEE
被回收者的数量
ORA_SQL_TXT
触发了触发器的SQL语句的行数。
ORA_SYSEVENT
导致DDL触发器被触发的时间
ORA_WITH_GRANT_OPTION
如果授权带有grant选项，返回true。否则false
更多属性函数请参考官方文档PL/SQL Language Reference -> Triggers and
Oracle Database Data Transfer Utilities
使用事件和属性
Examples:
--创建数据库对象时发出警告，删除数据库对象时阻止
CREATE OR REPLACE TRIGGER HR.no_drop
BEFORE DDL ON DATABASE
BEGIN
IF ORA_SYSEVENT = 'CREATE'
THEN
DBMS_OUTPUT.PUT_LINE('Warning !!! You have created a '||
ORA_DICT_OBJ_TYPE ||' called '||
ORA_DICT_OBJ_NAME|| '; UserName(creater):'||
ORA_DICT_OBJ_OWNER||'; IP:'||
ORA_CLIENT_IP_ADDRESS||'; event:'||
ORA_SYSEVENT);
ELSIF ORA_SYSEVENT = 'DROP'
THEN
RAISE_APPLICATION_ERROR (-20000,
'Cannot create the ' || ORA_DICT_OBJ_TYPE ||
' named ' || ORA_DICT_OBJ_NAME ||
' as requested by ' || ORA_DICT_OBJ_OWNER);
END IF;
END;
--操作了数据库表的哪一列
CREATE OR REPLACE TRIGGER preserve_app_cols
AFTER ALTER ON SCHEMA
DECLARE
-- cursor to get columns in a table
CURSOR curs_get_columns (cp_owner VARCHAR2, cp_ta