PLSQL入门与精通（第69章：DDL触发器）

77 阅读 0 评论 51 点赞

我是靠谱客的博主感性季节，最近开发中收集的这篇文章主要介绍PLSQL入门与精通（第69章：DDL触发器），觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

前两次我们解说了“复合触发器”。

总结一下，复合触发器简单来说是将4种DML触发器（BEFORE的SQL文、BEFORE的行、AFTER的行、AFTER的SQL文）整理一块的东西。
这些都属于DML触发器,是最常见的触发器,是DML语句（INSERT、UPDATE、DELETE）执行的时候自动启动的PL/SQL程序。

但是，除了DML触发器之外其他种类的触发器也有很多。
例如，“DDL语句”启动的触发器、在“登录”或“注销”数据库时启动的触发器、在某个“错误发生的时候”启动的触发器、
在“启动”数据库、“停止”数据库时启动的触发器等各种各样的触发器。

从本次开始介绍一下DML触发器以外的几个触发器。
本次介绍一下“DDL触发器”。

“DDL触发器”是通过DDL语句、即“CREATE”、“ALTER”、“DROP”执行时自动启动的触发器。
也就是说，在执行CREATE文、ALTER文、DROP文时会自动执行的触发器，此时，CREATE、ALTER、DROP操作的对象不限于表。
DDL触发器的事件CREATE、ALTER、DROP的操作对象可以是表，视图索引等等，只要有DDL语句执行，触发器就会自动启动。

另外，DDL触发器与DML触发器不同，DDL触发器不能指定具体的对象。
DML触发器，则必须有“ON表名”的描述，必须对特定的表（有时是视图）进行定义，
DDL触发器不指定具体的对象（表，试图等等），
因为CREATE、DROP操作的对象可能还不存在，从而无法编译。

我们马上来解说DDL触发器的语法。如下所示：
1 CREATE OR REPLACE TRIGGER 触发器名
2 BEFORE或AFTER CREATE OR DROP
3 ON SCHEMA或DATABASE
4 无名PL/SQL块

首先，第二行的“BEFORE或AFTER”语法，定义的那个事件导致的触发器。
DDL操作语句前启动时为“BEFORE”，DDL操作语句执行后启动时为“AFTER”。

同样，第2行的「CREATE OR ALTER OR DROP」是DDL的事件，当然和DDL时间无光的化这块也是可以省略的。

第3行的“ON”之后是“SCHEMA或DATABASE”，该描述可以是“SCHEMA”或“DATABASE”中的一个。
如果指定为“SCHEMA”的话，触发器发生的对象是该触发器的所有者的所有的对象（SCHEMA用户）。
如果指定「DATABASE」的话，触发器发生的对象是改数据库所有的用户都是对象。

刚才我们说了，DML触发器的“ON”之后可以指定特定的表名。
DDL触发器不能指定特定的对象，但是可以指定“SCHEMA”或“DATABASE”其中的一个。

第4行的“无名PL/SQL块”描述了触发器处理的内容。
在无名PL/SQL块中，如果要声明变量的话，需要从DECLARE关键字开始。

此外，根据处理内容的不同，可能需要事件类型（CREATE、ALTER、DROP）DDL操作对象的名称等。
在这种情况下，系统中定义了各种事件属性函数，可以通过使用这些事件属性函数获得我们想要的信息。

我们马上来介绍具体的例子。

接下来的例子是在执行DROP语句时启动的触发器，DROP的对象是SCOTT下的EMP表，
一发生删除这个表时就会提示错误：不能删除这个表。
一言以蔽之，想删除SCOTT.EMP这个表时，不能删除，要提示不可以删除的信息。
这个触发器是这个作用。

1 CREATE OR REPLACE TRIGGER TEST_DDL_TRIG
2 BEFORE DROP
3 ON DATABASE
4 DECLARE–无名PL/SQL块
5 V_SYSEVENT VARCHAR2(200);
6 V_DICT_OBJ_OWNER VARCHAR2(30);
7 V_DICT_OBJ_NAME VARCHAR2(30);
8 V_DICT_OBJ_TYPE VARCHAR2(20);
9 V_MESSAGE VARCHAR2(200);
10 BEGIN

11 /** 取得具体的时间 **/
12 V_SYSEVENT := ora_sysevent;

13 /** 获得对象所有者 **/
14 V_DICT_OBJ_OWNER := ora_dict_obj_owner;

15 /** 获取对象名称 **/
16 V_DICT_OBJ_NAME := ora_dict_obj_name;

17 /** 获取对象类型 **/
18 V_DICT_OBJ_TYPE := ora_dict_obj_type;

19 /** 删除SCOTT.EMP表时出错 **/
20 IF V_SYSEVENT = ‘DROP’ AND
21 V_DICT_OBJ_OWNER = ‘SCOTT’ AND
22 V_DICT_OBJ_TYPE = ‘TABLE’ AND
23 V_DICT_OBJ_NAME = ‘EMP’ THEN
24 RAISE_APPLICATION_ERROR(-20000, ‘SCOTT.EMP表无法被删除’）；
25 END IF;
26 END ;
/

触发器已创建。

我们解说如下：

首先，第2行的「BEFORE DROP」的描述，这个触发器是在执行DROP文之前启动的DDL触发器。
并且第3行的「ON DATABASE」的描述，表示这是这个数据库所有用户的操作发生水启动的触发器。
如果该部分是“ON SCHEMA”，则该触发器启监视对象时该触发器所属的用户，
该触发器以外的用户执行DROP语句时，该触发器将不会被启动。
如果指定“ON SCHEMA”的话，用户可以删除SCOTT.EMP表不会触发该触发器，这一点要注意。

另外，创建“ON DATABASE”的触发器，创建者需要“ADMINISTER DATABASE TRIGGER”系统权限，般由数据库管理者来创建该种类的触发器。

接着从第11行到第18行进行变量的赋值，赋值句用（：=），右边的小写字母ora_xxxx_xxx等等都是事件属性相关的系统函数。

这些事件属性相关的系统函数，可以获得各种各样的事件类型（“CREATE”、“ALTER”、“DROP”）、例如：对象的所有者、姓名、类型等信息。

例子的里边的事件属性函数是典型的函数，除此之外还有各种事件属性函数。

然后，第20行到第25行：SCOTT.EMP判断表的DROP处理，抛出用户自定义的系统错误，错误信息包含上边取得的变量值。
抛出用户自定义的系统错误：「RAISE_APPLICATION_ERROR(-20000,'SCOTT.EMP无法删除表格“）；”
抛出用户自定义的系统错误的话，也就是触发器中发生了错误，执行的事件操作会被取消。
第二行无论是“BEFORE”也好，“AFTER”也好都是一样，操作都会被取消。
所以第二行是AFTER SCOTT.EMP表DROP的删除是不会被执行的。

但是，在触发器中检查某个东西后出错的逻辑，一般来说是用“BEFORE”的。

我们来执行一下以上的例子吧：

SQL> DROP TABLE SCOTT.EMP;
DROP TABLE SCOTT.EMP
*
行1发生错误。:

在ORA-00604：递归SQL等级1中发生错误。
ORA-20000: SCOTT.EMP表无法被删除
ORA-06612:行21

如我们预想的一样，在删除SCOTT.EMP表，我们不想删除，自定义一个异常：ORA-20000,
从而让删除失败。

只要此触发器有效，任何用户都可以使用该触发器,确保无法删除SCOTT.EMP表。

以上就是简单地DDL触发器的例子。