《Oracle大型数据库基础开发教程》（微课视频版）课件 第10章 触发器

第10章触发器10.1触发器概述触发器是与表，视图或者数据库相关联的一段PL/SQL块或一个PL/SQL过程，以独立形式存放在数据库服务器中，在满足特定事件时自动执行，而用户不能显式调用，通常用于实现数据库中数据库约束等难以完成的服务业务规则或自动监视对数据库的操作，实现简单审计功能。1.触发器分类触发器DML触发器、替代触发器和数据库事件触发器三类。其中DML触发器的作用对象是表，替代触发器的作用对象是视图，数据库事件触发器的作用对象是数据库或者模式。10.1触发器概述2.触发器组成每个触发器通常包含以下几个部分：

（1）

触发事件：引起触发器被触发的事件。触发事件有多个，如：DML语句(INSERT,UPDATE,DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。（2）

触发时间：触发时间分为BEFORE和AFTER两种，其决定了触发事件和触发器的执行顺序。（3）

触发操作：也就是触发器的执行部分，该部分实现了触发器的主要功能。（4）触发对象：包括表、视图、模式和数据库。作用对象不同，触发器作用差别比较大。（5）

触发条件：通过触发条件的设置，使触发器触发时机更加严格，从而减少触发器的执行次数。触发条件通常出现在WHEN子句。（6）

触发级别。触发级别的设置会影响触发器执行次数，分为语句级触发器和行级触发器：10.1触发器概述3.几点注意事项编写触发器时，需要注意以下几点：（1）触发器没有参数。（2）同一个表可以有多个触发器，最多不超过12个，当有多个触发器时，要注意对触发器作用进行区分。（3）同一个表上面的DML触发器不宜太多，否则会显著影响对进行DML操作的性能。（4）DML语句可以直接出现在触发器执行部分，而DDL语句不能直接使用。（5）触发器执行部分不能直接或者间接使用事务处理语句commit,rollback和savepoint。10.2DML触发器DML触发器DML触发器是作用在表上的，当在表上执行某个DML操作时自动触发。1.创建DML触发器1)创建时要考虑问题创建DML触发器着重考虑触发时间、触发事件、触发对象和触发级别。（1）确定触发时间：指定触发器的触发时间是BEFORE还是AFTER。如果指定为BEFORE，则表示在执行DML操作之前触发，以便防止某些错误操作发生或实现某些业务规则；如果指定为AFTER，则表示在执行DML操作之后触发，以便记录该操作或做某些事后处理。（2）确定

触发事件：DML触发器触发事件可以是INSERT、UPDATE、DELETE中的一个或者多个，多个触发事件用逻辑运算服OR连接。（3）确定触发对象：确定触发器作用在哪个表上。（4）确定触发级别：确定是语句级触发器还是行级触发器。（5）触发条件的设置：当触发级别是行级触发器时，可以根据需求用when子句进行触发条件设置，从而使触发器触发时机更严格，以减小使用触发器对系统效率的影响。10.2DML触发器DML触发器2）3个谓词和2个伪记录DML触发器触发事件可能有多个，可以使用谓词区分当前执行的是哪个DML操作。（1）INSERTING：当触发事件是INSERT时，取值为TRUE，否则为FALSE。（2）UPDATING[（column_1,column_2,…,column_x）]：当触发事件是UPDATE

时，如果修改了column_x列，则取值为TRUE，否则为FALSE。其中column_x是可选的。（3）DELETING：当触发事件是DELETE时，取值为TRUE，否则为FALSE。当行级触发器被触发时，需要使用被插入、更新或删除的记录中的列值，有时要使用操作前、后列的值，此时需要用到两个伪记录:new和:old。其中:new用来获得操作后的值，:old用来获得操作前的值。当伪记录出现在when子句中时，要省略冒号。10.2DML触发器DML触发器3)DML触发器执行顺序同一个表上可以有多个DML触发器，此时DML触发器的执行顺序为：如果存在语句级BEFORE触发器，则在第一个DML操作之前，先执行一次语句级BEFORE触发器；若存在行级BEFORE触发器，则在执行每一行操作之前，都要先执行一次行级BEFORE触发器；若存在行级AFTER触发器，则在执行每一行操作之后，都要执行一次行级AFTER触发器；若存在语句级AFTER触发器，则在所有操作执行完成后，执行一次语句级AFTER触发器。10.2DML触发器DML触发器4)创建DML触发器创建DML触发器的语法：CREATE[ORREPLACE]TRIGGER触发器名{BEFORE|AFTER}{INSERT

|

DELETE

|

UPDATE

[OF

column

[,

column

…]]}ON表名[FOREACHROW][WHEN触发条件] DECLARE声明部分BEGIN主体部分END;10.2DML触发器例1：创建一个行级触发器，记录对职务为CLERK的雇员工资的修改，且当修改幅度超过200时才进行记录。用WHEN条件限定触发器。------首先创建一个日志表，用来存储相应记录-----createtablelog1(log_idintprimarykey,oper_namevarchar2(20),oper_datedate,oper_typevarchar2(10));------创建一个序列号，用于自动生成操作序号-----createsequenceseq1;10.2DML触发器例1：创建一个行级触发器，记录对职务为CLERK的雇员工资的修改，且当修改幅度超过200时才进行记录。用WHEN条件限定触发器。------创建触发器tri1-----createorreplacetriggertri1after--触发时间updateofsal–触发事件onemp–触发对象foreachrow–触发级别when((new.sal-old.sal)>200andold.job='CLERK')–触发条件begininsertintolog1(seq1.nextval,user,sysdate,'update');end;10.2DML触发器例2：用触发器实现emp表和dept表数据的级联更新和级联删除。createorreplacetriggertri2afterupdateordeleteondeptforeachrowbeginifupdatingthen--使用updating谓词判断当前是否执行的update操作updateempsetdeptno=:new.deptnowheredeptno=:old.deptno;elsifdeletingthen--使用deleting谓词判断当前是否执行的delete操作deletefromempwheredeptno=:old.deptno;endif;end;10.2DML触发器创建一个行级触发器tri4，实现的功能为：当修改emp表中员工工资时，限制只能修改工资低于3000的员工工资。createorreplacetriggertrg4beforeupdateofsalonempforeachrowbeginif(:old.sal>=3000)thenraise_application_error(-20001,'只能修改工资低于3000的员工工资。');endif;end;执行如下命令：updateempsetsal=2000whereename='SMITH';--更新成功updateempsetsal=2000whereename='SMITH';--更新失败，触发器阻止了更新操作10.3替代触发器替代触发器作用于视图上，用于当组成视图的基表是两个及两个以上或者对应的子查询中包含函数等表达式时，无法通过视图对基表更新的情况。1.创建替代触发器创建DML触发器的语法：CREATE[ORREPLACE]TRIGGER触发器名INSTEADOF{INSERT

|

DELETE

|

UPDATE

[OF

column

[,

column

…]]}ON视图名[FOREACHROW][WHEN触发条件]DECLARE声明部分BEGIN主体部分END;10.3替代触发器例1：视图子查询包含函数等表达式无法更新基表数据。---在scott用户下创建视图view_avgsal，查看每个部门的平均工资---Createtableemp_bakisselect*fromemp;--为避免约束影响，重新准备数据。createviewview_avgsalasselectdeptno,avg(sal)avgsalfromemp_bakgroupbydeptno;---通过视图对基表执行删除操作时失败---SQL>createviewview_avgsal2asselectdeptno,avg(sal)avgsalfromemp_bakgroupbydeptno;10.3替代触发器例1：视图子查询包含函数等表达式无法更新基表数据。---在view_avgsal视图上创建一个替代触发器tri_view1---createorreplacetriggertri_view1insteadofdeleteonview_avgsalBegindeletefromemp_bakwheredeptno=:old.deptno;End;

在执行上边通过删除进行的删除操作则可以成功。10.4数据库事件触发器数据库事件触发器既可以建立在一个模式上，又可以建立在整个数据库上。当建立在模式(SCHEMA)之上时，只有模式所指定用户的DDL操作和它们所导致的错误才激活触发器,

默认时为当前用户模式。当建立在数据库(DATABASE)之上时，该数据库所有用户的DDL操作和他们所导致的错误，用户的登录与退出以及数据库的启动和关闭均可激活触发器，建立在数据库上的系统事件触发器通常由系统超级管理员创建，普通用户没有权限创建。1.创建数据库事件触发器创建触发器的一般语法是:CREATE[ORREPLACE]TRIGGER触发器名{BEFORE|AFTER}{DDL事件1ORDDL事件2...]|数据库事件1OR数据库事件2...]}ON{DATABASE|[模式名.]SCHEMA}[WHEN(条件)]DECLARE声明部分BEGIN主体部分END;10.4数据库事件触发器10.4数据库事件触发器10.4数据库事件触发器

例1：数据库事件触发器建立在模式上：创建一个触发器tri_drop，通过触发器阻止对scott用户下test表的删除。---在SCOTT用户下创建一个数据表---Createtabletest(idnumber);--此时，创建完成后可以正常执行test表的删除操作。---创建tri_drop触发器---createorreplacetriggertri_dropBEFOREDROPONSCHEMAbeginifSYS.DICTIONARY_OBJ_NAME='test'thenRAISE_APPLICATION_ERROR(-20098,'不能删除test表!');endif;end;--当tri_drop触发器创建完