第6章触发器.

1第6章触发器•触发器是一种在发生数据库事件时自动运行的PL/SQL代码块，它与特定表上的DML操作相关联。注意触发器与数据库中其他过程的区别，过程或函数都由用户直接调用，而触发器的执行用户则不能直接调用。Oracle会在相应的事件发生时，自动调用触发器。2本章学习目标：•触发器的组成•创建DML触发器•创建替代触发器•创建系统事件触发器•创建用户事件触发器•重编译触发器36.1触发器的组成•触发器作为数据库中的一种命名程序块，用户在创建解发器时必须像创建存储过程和函数等命名程序块一样，为触发器指定一个惟一的名称。触发条件就是那些可以导致触发器执行的事件，这些事件可以包括如下几种：–在表或视图上的操作语句，如INSERT、UPDATE和DELETE操作–在模式对象上的数据定义语句，如CREATE、ALTER和DROP–数据库的启动和关闭–系统错误或者系统的各种活动•主要组成部分：–触发器名–触发条件–触发器限制–触发器主体•触发器类似于函数和过程，它们都是具有声明部分、执行部分和异常处理部分的命名PL/SQL块。过程是显式地通过过程调用从其他块中执行的，同时，过程调用可以传递参数。与之相反,触发器是在事件发生时隐式地运行的，并且触发器不能接收参数。•运行触发器的方式叫做激发（firing）触发器，触发事件可以是对数据库表的DML（INSERT、UPDATE或DELETE）操作或某种视图的操作(View)。•从Oracle8i开始，Oracle把触发器功能扩展到了可以激发系统事件，如数据库的启动和关闭，以及某种DDL操作。•触发器可以用于下列情况：–实现无法通过表创建声明实现的复杂完整性限制。–通过记录修改内容和修改者来审计表中的信息。–在表内容发生变更时，自动通知其他程序采取相应的处理。–在订阅发布环境下，发布有关各种事件的信息。•有三种主要的触发器类型：DML、替代触发器和系统触发器。触发条件（触发事件）•触发器必须由事件才能触发。触发器的触发事件分可为3类，分别是DML事件、DDL事件和数据库事件。•每类事件包含若干个事件，如下页表所示。数据库的事件是具体的，在创建触发器时要指明触发的事件。表1触发器事件种类关键字含义INSERT在表或视图中插入数据时触发UPDATE修改表或视图中的数据时触发DML事件(3种)DELETE在删除表或视图中的数据时触发CREATE在创建新对象时触发ALTER修改数据库或数据库对象时触发DDL事件(3种)DROP删除对象时触发STARTUP数据打开时触发SHUTDOWN在使用NORMAL或IMMEDIATE选项关闭数据库时触发LOGON当用户连接到数据库并建立会话时触发LOGOFF当一个会话从数据库中断开时触发数据库事件(5种)SERVERERROR发生服务器错误时触发86.2Oracle触发器的类型Oracle具有不同类型的触发器，可以让开发者实现不同的功能。Oracle提供的触发器类型主要包括：DML触发器当对表进行DML操作时触发，可以在DML操作前或操作后进行触发。替代触发器替代触发器是Oracle来用替换所使用的实际语句而执行的触发器。数据定义语言(DDL)触发器系统触发器系统触发器就是在Oracle数据库系统的事件中进行触发，如上面曾说过的Oracle系统的启动与关闭等。表2各类触发器的作用种类简称作用数据操纵语言触发器DML触发器创建在表上，由DML事件引发的触发器替代触发器INSTEADOF触发器创建在视图上，用来替换对视图进行的插入、删除和修改操作数据定义语言触发器DDL触发器定义在模式上，触发事件是数据库对象的创建和修改数据库事件触发器—定义在整个数据库或模式上，触发事件是数据库事件1.DML触发器•DML触发器的要点•DML触发器是定义在表上的触发器，由DML事件引发。编写DML触发器的要素是：•确定触发的表，即在其上定义触发器的表。•确定触发的事件，DML触发器的触发事件有INSERT、UPDATE和DELETE三种，说明见表1。•确定触发时间。触发的时间有BEFORE和AFTER两种，分别表示触发动作发生在DML语句执行之前和语句执行之后。•确定触发级别，有语句级触发器和行级触发器两种。语句级触发器表示SQL语句只触发一次触发器，行级触发器表示SQL语句影响的每一行都要触发一次。•由于在同一个表上可以定义多个DML触发器，因此触发器本身和引发触发器的SQL语句在执行的顺序上有先后的关系。它们的顺序是：•如果存在语句级BEFORE触发器，则先执行语句级BEFORE触发器。•在SQL语句的执行过程中，如果存在行级BEFORE触发器，则SQL语句在对每一行操作之前，都要先执行一次行级BEFORE触发器，然后才对行进行操作。•行操作•如果存在行级AFTER触发器，则SQL语句在对每一行操作之后，都要再执行一次行级AFTER触发器。•如果存在语句级AFTER触发器，则在SQL语句执行完毕后，要最后执行一次语句级AFTER触发器。DML触发器还有一些具体的问题，说明如下：•如果有多个触发器被定义成为相同时间、相同事件触发，且最后定义的触发器是有效的，则最后定义的触发器被触发，其他触发器不执行。•一个触发器可由多个不同的DML操作触发。在触发器中，可用INSERTING、DELETING、UPDATING谓词来区别不同的DML操作。这些谓词可以在IF分支条件语句中作为判断条件来使用。•在行级触发器中，用:new和:old(称为伪记录)来访问数据变更前后的值。但要注意，INSERT语句插入一条新记录，所以没有:old记录，而DELETE语句删除掉一条已经存在的记录，所以没有:new记录。UPDATE语句既有:old记录，也有:new记录，分别代表修改前后的记录。引用具体的某一列的值的方法是：•:old.字段名或:new.字段名•When后面引用old和new不能在前面加冒号。•触发器体内禁止使用COMMIT、ROLLBACK、SAVEPOINT语句，也禁止直接或间接地调用含有上述语句的存储过程。•定义一个触发器时要考虑上述多种情况，并根据具体的需要来决定触发器的种类。2DML触发器的创建•创建DML触发器需要CREATETRIGGER系统权限。创建DML触发器的语法如下：•CREATE[ORREPLACE]TRIGGER触发器名•{BEFORE|AFTER|INSTEADOF}触发事件1[OR触发事件2...]•ON[schema.]表名|[schema.]视图名•WHEN触发条件•[FOREACHROW]•DECLARE•声明部分•BEGIN•主体部分•END;•其中：•ORREPLACE：表示如果存在同名触发器，则覆盖原有同名触发器。•BEFORE、AFTER和INSTEADOF：说明触发器的类型。•WHEN触发条件：表示当该条件满足时，触发器才能执行。•触发事件：指INSERT、DELETE或UPDATE事件，事件可以并行出现，中间用OR连接。•对于UPDATE事件，还可以用以下形式表示对某些列的修改会引起触发器的动作：•UPDATEOF列名1，列名2...•ON表名：表示为哪一个表创建触发器。•FOREACHROW：表示触发器为行级触发器，省略则为语句级触发器。删除触发器的语法•DROPTIRGGER触发器名•可以通过命令设置触发器的可用状态，使其暂时关闭或重新打开，即当触发器暂时不用时，可以将其置成无效状态，在使用时重新打开。该命令语法如下：•ALTERTRIGGER触发器名{DISABLE|ENABLE}•其中，DISABLE表示使触发器失效，ENABLE表示使触发器生效。•同存储过程类似，触发器可以用SHOWERRORS检查编译错误。3行级触发器的应用•在行级触发器中，SQL语句影响的每一行都会触发一次触发器，所以行级触发器往往用在对表的每一行的操作进行控制的场合。若在触发器定义中出现FOREACHROW子句，则为行级触发器。•创建包含插入、删除、修改多种触发事件的触发器DML_LOG，对EMP表的操作进行记录。用INSERTING、DELETING、UPDATING谓词来区别不同的DML操作。•在创建触发器之前，需要先创建事件记录表LOGS，该表用来对操作进行记录。该表的字段含义解释如下：•LOG_ID：操作记录的编号，数值型，它是该表的主键，由序列自动生成。•LOG_TABLE：进行操作的表名，字符型，非空，该表设计成可以由多个触发器共享使用。比如我们可以为dept表创建类似的触发器，同样将操作记录到该表。•LOG_DML：操作的动作，即INSERT、DELETE或UPDATE三种之一。•LOG_KEY_ID：操作时表的主键值，数值型。之所以记录表的主键，是因为主键是表的记录的惟一标识，可以识别是对哪一条记录进行了操作。对于emp表，主键是empno。•LOG_DATE：操作的日期，日期型，取当前的系统时间。•LOG_USER：操作者，字符型，取当时的操作者账户名。比如登录SCOTT账户进行操作，在该字段中，记录账户名为SCOTT。•步骤1：在SQL*Plus中登录STUDENT账户，创建如下的记录表LOGS：–CREATETABLElogs(–LOG_IDNUMBER(10)PRIMARYKEY,–LOG_TABLEVARCHAR2(10)NOTNULL,–LOG_DMLVARCHAR2(10),–LOG_KEY_IDNUMBER(10),–LOG_DATEDATE,–LOG_USERVARCHAR2(15)–);•执行结果：•表已创建。步骤2：创建一个LOGS表的主键序列LOGS_ID_SEQ：•CREATESEQUENCElogs_id_squINCREMENTBY1•STARTWITH1MAXVALUE9999999NOCYCLENOCACHE;•执行结果：•序列已创建。步骤3：创建和编译以下触发器：•CREATEORREPLACETRIGGERDML_LOG•BEFORE--触发时间为操作前•DELETEORINSERTORUPDATE--由三种事件触发•ONemp•FOREACHROW--行级触发器•BEGIN–IFINSERTINGTHEN–INSERTINTOlogsVALUES(logs_id_squ.NEXTVAL,'EMP','INSERT',:new.empno,SYSDATE,USER);–ELSIFDELETINGTHEN–INSERTINTOlogsVALUES(logs_id_squ.NEXTVAL,'EMP','DELETE',:old.empno,SYSDATE,USER);–ELSE–INSERTINTOlogsVALUES(logs_id_squ.NEXTVAL,'EMP','UPDATE',:new.empno,SYSDATE,USER);–ENDIF;–END;•如果改为语句级别，看有何变化？如果before改成after呢？•执行结果：•触发器已创建•步骤4：在EMP表中插入记录：•INSERTINTOemp(empno,ename,job,sal)VALUES(8001,'MARY','CLERK',1000);•COMMIT;•执行结果：•已创建1行。•提交完成。•步骤5：检查LOGS表中记录的信息：•SELECT*FROMLOGS;•执行结果为：•LOG_IDLOG_TABLELOG_DMLLOG_KEY_IDLOG_DATELOG_USER•--------------------------------------------------------------------------------------------------------------•1EMPINSERT800129-3月-04STUDENT•已选择1行。•说明：本例中在emp表上创建了一个由INSERT或DELETE或UPDATE事件触发的行级触发器，触发器的名称是LOG_EMP。对于不同的操作，记录的内容不同