第13章 触发器

1、第第13章章 触触 发发 器器本章目标本章目标n理解什么是触发器理解什么是触发器n掌握如何使用掌握如何使用BEFORE和和AFTER触发触发器器n掌握行触发器和语句触发器的区别与使掌握行触发器和语句触发器的区别与使用用n掌握如何使用掌握如何使用INSTEAD OF触发器触发器13.1 什么是触发器什么是触发器n基本概念基本概念n数据库触发器是存储于数据库的命名数据库触发器是存储于数据库的命名PL/SQL语句块，当触发事件发生时它们会隐含执行。语句块，当触发事件发生时它们会隐含执行。n执行触发器的活动被称为触发触发器。执行触发器的活动被称为触发触发器。n触发事件触发事件n处理数据库表的处理数据库

2、表的DML语句语句n特定用户在特定模式下，或者任何用户执行的特定用户在特定模式下，或者任何用户执行的DDL语句语句n系统事件系统事件n用户事件用户事件n主要用于：主要用于：n执行不能通过使用完整性约束来定义的复杂业执行不能通过使用完整性约束来定义的复杂业务规则务规则n维护复杂的安全规则维护复杂的安全规则n自动生成衍生列的值自动生成衍生列的值n收集有关访问数据库表的统计信息收集有关访问数据库表的统计信息n防止无效的事务防止无效的事务n提供值审计提供值审计创建触发器创建触发器n语法语法CREATE OR REPLACE TRIGGER CREATE OR REPLACE TRIGGER trigg

3、er_nametrigger_name BEFORE | AFTER BEFORE | AFTER trigger_eventtrigger_event ON ON table_nametable_name FOR EACH ROW FOR EACH ROW FOLLOWS FOLLOWS anther_triggeranther_trigger ENABLE | DISABLE ENABLE | DISABLE WHEN WHEN coditioncodition trigger_bodytrigger_body; ;Oracle 11g引入的引入的BEFORE触发器触发器nBEFORE触发

4、器适用于：触发器适用于：n当在当在INSERT或者或者UPDATE语句完成之前，触语句完成之前，触发器需要提供衍生列的值时；发器需要提供衍生列的值时；n当触发器决定当触发器决定INSERT、UPDATE或者或者DELETE语句是否应该允许完成的时候。语句是否应该允许完成的时候。create or replace trigger tr_emp_insert before insert on emp for each rowbegin :new.hiredate := sysdate;end; 【例【例13-1】创建】创建BEFORE触发器，使用当前系触发器，使用当前系统日期作为新增雇员的雇佣日期

5、。统日期作为新增雇员的雇佣日期。注意：伪记录注意：伪记录 :old 和和 :new 只能用于行触发器。只能用于行触发器。:old 和和 :new 伪记录伪记录触发语句触发语句:old:newINSERT未定义，所有字段均为未定义，所有字段均为NULL语句结束时，将要被插语句结束时，将要被插入的值入的值UPDATE 更新前行的原始值更新前行的原始值语句结束时，将要被更语句结束时，将要被更新的值新的值DELETE行被删除前的原始值行被删除前的原始值未定义，所有字段均为未定义，所有字段均为NULLAFTER触发器触发器nAFTER触发器适用于：触发器适用于：n当触发器应该在当触发器应该在DML执行之

6、后被触发时执行之后被触发时n当触发器执行当触发器执行BEFORE触发器中未指明的动作触发器中未指明的动作时时create or replace trigger tr_log after delete on deptbegin insert into t_logs(log_time, log_user, log_opt) values(sysdate, user, DELETE ON dept);end; 【例【例13-2】创建】创建AFTER触发器，删除触发器，删除dept表表记录后，向记录后，向t_logs日志表中写入操作信息。日志表中写入操作信息。WHEN子句子句nWHEN子句只能在行触发

7、器中使用。子句只能在行触发器中使用。n如果在行触发器的定义中给出了如果在行触发器的定义中给出了WHEN子子句，触发器主体就只对满足句，触发器主体就只对满足WHEN所定义所定义条件的那些记录行执行。条件的那些记录行执行。n在在WHEN子句的条件中使用子句的条件中使用:new和和:old时，时，都不需要冒号作为前缀。都不需要冒号作为前缀。create or replace trigger tr_sal_2000 before update on emp for each row when (new.sal 2000)begin dbms_output.put_line(execute);end;

8、【例【例13-3】创建触发器，当雇员工资大于】创建触发器，当雇员工资大于2000时执行该触发器主体部分。时执行该触发器主体部分。create or replace trigger tr_sal_2000 before update on emp for each rowbegin if :new.sal 2000 then dbms_output.put_line(execute); end if;end; 等价于：等价于：触发器谓词触发器谓词谓词谓词行为特征行为特征INSERTING如果激活语句是如果激活语句是INSERT语句，就为语句，就为TRUE；否则就为否则就为FALSEUPDATIN

9、G如果激活语句是如果激活语句是UPDATE语句，就为语句，就为TRUE；否则就为否则就为FALSEDELETING如果激活语句是如果激活语句是DELETE语句，就为语句，就为TRUE；否则就为否则就为FALSEcreate or replace trigger tr_log after insert or update or delete on deptdeclare v_opt varchar2(100); begin if inserting then v_opt := INSERT ON dept; end if; if updating then v_opt := UPDATE ON

10、dept; end if; if deleting then v_opt := DELETE ON dept; end if; insert into t_logs(log_time, log_user, log_opt) values(sysdate, user, v_opt);end; 【例【例13-4】修改【例】修改【例13-2】创建的触发器，根据】创建的触发器，根据DML操作类型将操作信息写入日志表操作类型将操作信息写入日志表t_logs中。中。自治事务自治事务n自治事务是由其他事务（通常被称为主事务）发自治事务是由其他事务（通常被称为主事务）发起的独立事务。起的独立事务。n自治事务会

11、执行多个自治事务会执行多个DML语句，并且提交或者回语句，并且提交或者回滚操作，而不会提交或者回滚主事务执行的滚操作，而不会提交或者回滚主事务执行的DML语句。语句。n使用使用AUTONOMOUS_TRANSACTION编译命令，编译命令，可以定义一个自治事务。可以定义一个自治事务。n在语句块的声明部分使用：在语句块的声明部分使用：DECLARE PRAGMA AUTONOMOUS_TRANSACTION;create or replace trigger tr_log after delete on deptdeclare pragma autonomous_transaction;begi

12、n insert into t_logs(log_time, log_user, log_opt) values(sysdate, user, DELETE ON dept); commit;end; 【例【例13-5】使用自治事务修改【例】使用自治事务修改【例13-2】创】创建的触发器。建的触发器。13.2 触发器类型触发器类型n触发器类型触发器类型n行触发器行触发器n语句触发器语句触发器n二者的区别二者的区别n行触发器被触发的次数等于触发语句所影响的行触发器被触发的次数等于触发语句所影响的数据行数。数据行数。n语句触发器每执行一次触发语句，该触发器就语句触发器每执行一次触发语句，该触发器就

13、执行一次，与触发语句所影响的数据行数无关。执行一次，与触发语句所影响的数据行数无关。DML触发器的激活顺序触发器的激活顺序n执行执行BEFORE语句触发器语句触发器n对受该语句影响的每一行记录：对受该语句影响的每一行记录：n执行执行BEFORE行触发器行触发器n执行该执行该DML语句本身语句本身n执行执行AFTER行触发器行触发器n执行执行AFTER语句触发器语句触发器INSTEAD OF触发器触发器nINSTEAD OF触发器只能定义于数据库视触发器只能定义于数据库视图，作为行触发器创建。图，作为行触发器创建。ninstead-of触发器用于以下两种情况：触发器用于以下两种情况：n允许修改一

14、个本来无法修改的视图。允许修改一个本来无法修改的视图。n修改视图中嵌套表列的列。修改视图中嵌套表列的列。无法修改的视图无法修改的视图n如果某视图查询执行如下任何一个操作，或如果某视图查询执行如下任何一个操作，或者包含如下任何结果，则视图就不能被者包含如下任何结果，则视图就不能被INSERT INSERT 、 UPDATEUPDATE或者或者DELETEDELETE语句修改：语句修改：n集合操作，如集合操作，如UNIONUNION、UNION ALLUNION ALL、INTERSECT INTERSECT 和和 MINUSMINUSn分组函数，如分组函数，如COUNTCOUNT、 AVGAVG

15、、SUMSUM、MAX MAX 和和 MINMINnGROUP BYGROUP BY语句语句nCONNECT BYCONNECT BY或者或者START WITHSTART WITH语句语句nDISTINCTDISTINCT操作符操作符nROWNUMROWNUM伪列伪列create or replace view v_Empas select * from emp where rownum = 5;【例【例13-6】创建视图，返回前】创建视图，返回前5名雇员信息。名雇员信息。思考：能否执行如下删除操作？思考：能否执行如下删除操作？delete from v_Emp where empno =

16、7369;create or replace trigger tr_del_v_emp instead of delete on v_empbegin delete from emp where empno = :old.empno;end; 【例【例13-6】创建】创建INSTEAD OF触发器，以完触发器，以完成删除操作。成删除操作。思考：为什么在思考：为什么在INSTEAD OF触发器中，没有使用触发器中，没有使用FOR EACH ROW选项，却可以使用选项，却可以使用:OLD伪记录？伪记录？系统触发器系统触发器n系统触发器的激发是基于两种不同的事件：系统触发器的激发是基于两种不同的事件

17、：nDDL事件事件n数据库事件数据库事件nDDL事件包括事件包括CREATE、ALTER或或DROP语语句句n数据库事件包括数据库服务器的启动数据库事件包括数据库服务器的启动/关闭关闭事件，用户的登录事件，用户的登录/断开事件，以及服务器断开事件，以及服务器错误。错误。创建系统触发器创建系统触发器n语法语法CREATE OR REPLACE TRIGGER CREATE OR REPLACE TRIGGER schemaschema.trigger_nametrigger_name BEFORE | AFTER BEFORE | AFTER ddl_event_listddl_event_li

18、st | | database_event_listdatabase_event_list ON DATABASE | ON DATABASE | schemaschema.SCHEMA.SCHEMA when_clausewhen_clause trigger_bodytrigger_body; ;注意：创建系统触发器必须拥有系统权限注意：创建系统触发器必须拥有系统权限ADMINISTER DATABASE TRIGGERADMINISTER DATABASE TRIGGER系统系统DDL事件和数据库事件事件和数据库事件事件事件允许时机允许时机说明说明STARTUPAFTER启动数据库实例的

19、时候激活启动数据库实例的时候激活SHUTDOWNBEFORE关闭数据库实例的时候激活。如关闭数据库实例的时候激活。如果数据库是非正常退出的，这个果数据库是非正常退出的，这个事件可能就不会激活事件可能就不会激活SERVERERRORAFTER只有发生错误，该事件就会激活只有发生错误，该事件就会激活LOGONAFTER用户成功连接到数据库以后激活用户成功连接到数据库以后激活LOGOFFBEFORE用户开始断开数据库连接的时候用户开始断开数据库连接的时候激活激活CREATEBEFORE, AFTER创建模式对象之前或之后激活创建模式对象之前或之后激活ALTERBEFORE, AFTER更改模式对象之

20、前或之后激活更改模式对象之前或之后激活DROPBEFORE, AFTER删除模式对象之前或之后激活删除模式对象之前或之后激活数据库与模式触发器数据库与模式触发器 n系统触发器可以在数据库级别或模式级别定义，系统触发器可以在数据库级别或模式级别定义， DATABASE DATABASE 和和 SCHEMASCHEMA关键字决定了给定系统触发关键字决定了给定系统触发器的级别。器的级别。n数据库级别的触发器不管触发事件何时发生都将数据库级别的触发器不管触发事件何时发生都将激发，而模式级别触发器只有在指定的模式的触激发，而模式级别触发器只有在指定的模式的触发事件发生时才会激发。发事件发生时才会激发。【

21、例【例13-7】数据库触发器与模式触发器的区别】数据库触发器与模式触发器的区别1.以以sys用户登录，创建用户用户登录，创建用户userA、userB，并授与，并授与DBA权限；权限；2.以以userA用户登录，创建表用户登录，创建表t_logon，用以记录用，用以记录用户的登录信息；户的登录信息；3.创建数据库触发器创建数据库触发器tr_LogOn_all；4.以以userB用户登录，创建模式触发器用户登录，创建模式触发器tr_LogOn_userB；5.在在SQL *Plus中分别以中分别以userA、userB用户登录，用户登录，查看查看t_logon表中的记录。表中的记录。- 创建数据

22、库触发器创建数据库触发器create or replace trigger tr_LogOn_all after logon on databasebegin insert into t_logon values(1, user, tr_LogOn_all);end;- 创建创建模式模式触发器触发器create or replace trigger tr_LogOn_UserB after logon on schemabegin insert into userA.t_logon values(2, user, tr_LogOn_UserB);end; userB.SCHEMA事件的属性函数

23、事件的属性函数属性函数属性函数返回类型返回类型对应的系统事件对应的系统事件说明说明ORA_DATABASE_NAMEVARCHAR2(50)所有事件所有事件返回数据库的名称返回数据库的名称ORA_DICT_OBJ_NAMEVARCHAR2(30)CREATE, ALTER, DROP返回在其上执行返回在其上执行DDL操作的数操作的数据字典对象的名称据字典对象的名称ORA_DICT_OBJ_OWNERVARCHAR2(30)CREATE, ALTER, DROP返回在其中执行返回在其中执行DDL操作的数操作的数据字典对象的所有者据字典对象的所有者ORA_DICT_OBJ_TYPEVARCHAR2

24、(20)CREATE, ALTER, DROP返回在其上执行返回在其上执行DDL操作的数操作的数据字典对象的类型据字典对象的类型ORA_INSTANCE_NUMNUMBER所有事件所有事件返回数据库实例数返回数据库实例数ORA_IS_SERVERERRORBOOLEANSERVERERROR以一个错误号为参数，如果错以一个错误号为参数，如果错误堆栈中有这个错误号，该函误堆栈中有这个错误号，该函数返回数返回TRUEORA_LOGIN_USERVARCHAR2(30)所有事件所有事件返回已登录用户名返回已登录用户名ORA_SERVER_ERRORNUMBERSERVERERROR返回错误堆栈中给定

25、位置上的返回错误堆栈中给定位置上的错误编号错误编号ORA_SYSEVENTVARCHAR2(20)所有事件所有事件触发该触发器的系统事件名触发该触发器的系统事件名create or replace trigger tr_logCreations after create on schemabegin insert into ddl_creations(user_id, object_type, object_name, object_owner, creation_time) values(user, ora_dict_obj_type, ora_dict_obj_name, ora_dict

26、_obj_owner, sysdate); end; 【例【例13-8】创建系统触发器，记录创建模式】创建系统触发器，记录创建模式对象的相关信息。对象的相关信息。系统触发器和事务系统触发器和事务 n根据触发事件的不同，系统触发器事务的行为方根据触发事件的不同，系统触发器事务的行为方式也有所不同。系统触发器要么以独立事务（该式也有所不同。系统触发器要么以独立事务（该独立事务会在触发器成功完成后提交）的形式激独立事务会在触发器成功完成后提交）的形式激活，要么就以当前用户事务的部分事务的形式进活，要么就以当前用户事务的部分事务的形式进行激活。行激活。nSTARTUPSTARTUP，SHUTDOWNS

27、HUTDOWN，SEVERERRORSEVERERROR和和LOGONLOGON触发器触发器会以独立事务的形式激活，而会以独立事务的形式激活，而LOGOFFLOGOFF和和DDLDDL触发器触发器则会以当前用户事务的部分事务的形式进行激活。则会以当前用户事务的部分事务的形式进行激活。系统触发器和事务系统触发器和事务 n触发器所执行的工作一般都会被无条件地提交。触发器所执行的工作一般都会被无条件地提交。在在DDLDDL触发器中，当前事务（也就是触发器中，当前事务（也就是CREATECREATE、ALTERALTER或或DROPDROP语句）将自动提交，而这种提交也提语句）将自动提交，而这种提交也

28、提交了触发器所完成的工作。交了触发器所完成的工作。LOGOFFLOGOFF触发器中的工触发器中的工作，也会以会话中最后一个事务的部分事务的形作，也会以会话中最后一个事务的部分事务的形式被提交。式被提交。 n注意：注意：无论如何系统触发器一般都会被提交，所无论如何系统触发器一般都会被提交，所以将它们声明为自治事务没有什么意义。以将它们声明为自治事务没有什么意义。系统触发器和系统触发器和WHEN子句子句 n系统触发器也可以使用系统触发器也可以使用 WHEN WHEN 子句来指定触发器子句来指定触发器激活的条件。激活的条件。n对每一种系统触发器所指定的条件类型有如下限对每一种系统触发器所指定的条件类

29、型有如下限制：制：nSTARTUP STARTUP 和和 SHUTDOWN SHUTDOWN 触发器不能带有任何条件。触发器不能带有任何条件。nSERVERERROR SERVERERROR 触发器只可以使用触发器只可以使用 ERRNO ERRNO 测试来检查具测试来检查具体错误。体错误。nLOGON LOGON 和和 LOGOFF LOGOFF 触发器可以使用触发器可以使用 USERID USERID 或或 USERNAME USERNAME 测试来检查用户标识符或用户名。测试来检查用户标识符或用户名。nDDL DDL 触发器可以检查正在被修改的对象的名称和类型，触发器可以检查正在被修改的对

30、象的名称和类型，也可以检查用户的也可以检查用户的 ID ID 或名称。或名称。 其他触发器问题其他触发器问题 n触发器名称的命名空间（触发器名称的命名空间（Name-space）n使用触发器的各种限制和不同种类的触发使用触发器的各种限制和不同种类的触发器体。器体。n与触发器有关的权限问题与触发器有关的权限问题 触发器名称触发器名称 n命名空间：是一组合法的可供对象作为名字使用命名空间：是一组合法的可供对象作为名字使用的标识符。过程、包和表都共享同一个命名空间，的标识符。过程、包和表都共享同一个命名空间，在一个数据库模式范围内，同一命名空间内的所在一个数据库模式范围内，同一命名空间内的所有的对象

31、必须具有唯一的名称。有的对象必须具有唯一的名称。n触发器使用的是一个独立的命名空间。也就是说，触发器使用的是一个独立的命名空间。也就是说，触发器可以有与表和过程相同的名称。然而，在触发器可以有与表和过程相同的名称。然而，在一个模式范围内，给定的名称只能用于一个触发一个模式范围内，给定的名称只能用于一个触发器。器。 n提示：提示：虽然触发器可以与表同名，但建议一般不虽然触发器可以与表同名，但建议一般不要这样使用。要这样使用。对触发器的限制对触发器的限制n注意事项注意事项n触发器不会执行事务控制语句触发器不会执行事务控制语句n触发器调用的任何函数或者过程不会执行事务触发器调用的任何函数或者过程不会执行事务控制语句控制语句n不允许在触发器体中声明不允许在触发器体中声明LONG或者或者LONG RAW变量变量n如果删除一个表，则在该表上所定义的数据库如果删除一个表，则在该表上所定义的数据库触发器也会被删除触发器也会被删除触发器主体触发器主体 n在在Oracle8i之前的版本中，触发器主体必须是之前的版本中，触发器主体必须是PL/SQL语句块。语句块。n在在Oracle8i及更高版本中，触发器主体可以包括及更高版本中，触发器主体可以包括CALL语句。被调用的过程既可以是语句。被调用的过程既可以是PL/SQL存储存储子程序，也可以是