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事件

可用属性

Oracle 提供了一系列的函数用来提供关于什么触发了DDL触发器以及触发器的状态灯信息。上面那个触发器的例子就使用了属性。

DDL触发器事件以及属性函数

更多属性函数请参考官方文档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;
/

最后

以上就是现实棒棒糖为你收集整理的Oracle触发器3-DDL触发器创建DDL触发器可用事件可用属性使用事件和属性的全部内容，希望文章能够帮你解决Oracle触发器3-DDL触发器创建DDL触发器可用事件可用属性使用事件和属性所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。