Oracle触发器3-DDL触发器
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 ' || v_priv_list (counter)
);
END LOOP;
ELSE
DBMS_OUTPUT.put_line ('I have no idea what was granted');
END IF;
FOR counter IN 1 .. v_num_grantees
LOOP
DBMS_OUTPUT.put_line (
CHR (9) || 'Grant Recipient ' || v_grantee_list (counter)
);
END LOOP;
END;
/
