Oracle触发器3-DDL触发器
Oracle触发器2-DML触发器
http://www.zzzyk.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_table VARCHAR2)
IS
SELECT column_name
FROM all_tab_columns
WHERE owner = cp_owner AND table_name = cp_table;
BEGIN
-- if it was a table that was altered...
IF ora_dict_obj_type = 'TABLE'
THEN
-- for every column in the table...
FOR v_column_rec IN curs_get_columns (
ora_dict_obj_owner,
ora_dict_obj_name
)
LOOP
-- if the current column was the one that was altered then say so
IF ora_is_alter_column (v_column_rec.column_name)
THEN
-- if the table/column is core?
IF is_application_column (
ora_dict_obj_owner,
ora_dict_obj_name,
v_column_rec.column_name
)
THEN
RAISE_APPLICATION_ERROR (
-20001,
'Cannot alter core application attributes'
);
END IF; -- table/column is core
END IF; -- current column was altered
END LOOP; -- every column in the table
END IF; -- table was altered
END;
--属性函数返回值列表
CREATE OR REPLACE TRIGGER hr.what_privs
AFTER GRANT ON SCHEMA
DECLARE
v_grant_type VARCHAR2 (30);
v_num_grantees BINARY_INTEGER;
v_grantee_list ora_name_list_t;
v_num_privs BINARY_INTEGER;
v_priv_list ora_name_list_t;
BEGIN
v_grant_type := ora_dict_obj_type;
v_num_grantees := ora_grantee (v_grantee_list);
v_num_privs := ora_privilege_list (v_priv_list);
IF v_grant_type = 'ROLE PRIVILEGE'
THEN
DBMS_OUTPUT.put_line (
CHR (9) || 'The following roles/privileges were granted'
);
FOR counter IN 1 .. v_num_privs
LOOP
DBMS_OUTPUT.put_line (
CHR (9) || CHR (9) || 'Privilege ' || v_priv_list (counter)
);
END LOOP;
ELSIF v_grant_type = 'OBJECT PRIVILEGE'
THEN
DBMS_OUTPUT.put_line (
CHR (9) || 'The following object privileges were granted'
);
FOR counter IN 1 .. v_num_privs
LOOP
DBMS_OUTPUT.put_line (
CHR (9) || CHR (9) || 'Privilege ' || v_priv_list (counter)
);
END LOOP;
DBMS_OUTPUT.put (CHR (9) || 'On ' || ora_dict_obj_name);
IF ora_with_grant_option
THEN
DBMS_OUTPUT.put_line (' with grant option');
ELSE
DBMS_OUTPUT.put_line ('');
END IF;
ELSIF v_grant_type = 'SYSTEM PRIVILEGE'
THEN
DBMS_OUTPUT.put_line (
CHR (9) || 'The following system privileges were granted'
);
FOR counter IN 1 .. v_num_privs
LOOP
DBMS_OUTPUT.put_line (
CHR (9) || CHR (9) || 'Privilege '
