Oracle第9章触发器操作

1、第9章 触发器操作主编：刘志成本章学习习导航本章学习习导航本章学习习要点（1）触发器器的基础知识识。（2）:OLD和:NEW变量。（3）SQLDeveloper创建、修修改、查查看、编编译和删删除触发发器。（4）PL/SQL创建、修修改、查查看、编编译和删删除触发发器。（5）触发器器的功能及使使用。（6）eBuy电子商城城中的典型触发发器建议课时时：8课时9.1触发器概概述触发器简简介触发器是是一种特特殊的存存储过程程，它与与数据表表紧密联联系，用用于保护护表中的的数据，当一个个定义了了特定类类型触发发器的基基表执行行插入、修改或或删除表表中数据据的操作作时，将将自动触触发触发发器中定定义的操

2、操作，以以实现数数据的一一致性和和完整性性。触发器拥拥有比数数据库本本身标准准的功能能更精细细和更复复杂的数数据控制制能力。触发器具具有以下下的作用用：（1）在安全性方面，触触发器可可以基于于数据库库的值使使用户具具有操作作数据库库的某种种权利。（2）在审计方面，触触发器可可以跟踪踪用户对对数据库库的操作作。（3）实现复杂的数数据完整整性规则则。（4）实现复复杂的非非标准的的数据库库相关完完整性规规则。触触发器可可以对数数据库中中相关的的表进行行连环更新新。（5）同步实时时地复制表表中的数数据。（6）自动计算算数据值值，如果数数据的值值达到了了一定的的要求，则进行行特定的的处理。例如，如果商商

3、品的数数量低于于5，则立即即给管理理人员发发送库存存报警信信息。9.1触发器概概述触发器类类型1触发器器的功能能（1）DML触发器当对表进进行DML操作时触触发，可可以在DML操作前或或操作后后触发。（2）替代触触发器是Oracle用来替换换所使用用的实际际语句而而执行的的触发器器。（3）系统触触发器在Oracle数据库系系统的事事件（Oracle系统的启启动与关关闭等）中进行行触发。（4）用户事事件触发发器指与数据据库定义义语句或或用户的的登录/注销等事事件相关关的触发发器。2触发事事件按触发事事件的不不同，触触发器可可以分为为插入型型（INSERT）、更新新型（UPDATE）和删除除型（D

4、ELETE）触发器器。对于插入入型触发发器，当当触发器器所在的的表发生生插入操操作时，触发器器将自动动触发执执行。对对于更新新型触发发器，当当触发器器所在的的表发生生更新操操作时，触发器器将自动动触发执执行。同同样，对对于删除除型触发发器，当当触发器器所在的的表发生生删除操操作时，触发器器也将自自动触发发执行。9.1触发器概概述触发器类类型3触发时时间根据指定定的事件件和触发发器执行行的先后后次序，触发器器可以分分为BEFORE型和AFTER型触发器器。如果在指指定的事事件（INSERT、UPDATE或者DELETE）之前执执行触发发器，这这类触发发器称为为BEFORE触发器；若在指指定的事事

5、件之后后执行触触发器，则称这这类触发发器为AFTER触发器。4触发级级别根据触发发级别的的不同，触发器器可以分分为行触发器器和语句触发发器。对于行触触发器，受触发发事件影影响的每每一行都都将引发发触发器器的执行行；而对对于语句句触发器器，触发发事件只只触发一一次，即即使有若若干行受受触发事事件的影影响，也也只执行行一次触触发操作作。9.1触发器概概述:OLD变量和:NEW变量在Oracle系统中，每个触触发器被被DML操作触发发时，会会产生两两个特殊殊的变量量:OLD和:NEW，分别代代表某数数据记录录行在修修改前和和修改后后的值。这两个个变量都都是系统统变量，由Oracle系统管理理，存储储

6、在内存存中，不不允许用用户直接接对其进进行修改改。:OLD和:NEW变量的结结构总是是与执行行DML操作的表表的结构构相同。当触发发器工作作完成以以后，这这两个变变量也随随之消失失。这两两个变量量的值是是只读的，即用用户不能能向这两两个变量量写入内内容，但但可以引引用变量量中的数数据。:OLD变量用于存储储DELETE和UPDATE操作所影响的的行的副副本。当当执行DELETE或UPDATE操作时，行从触触发表中中被删除除，并传传输到:OLD变量中。:NEW变量用于于存储INSERT和UPDATE操作所影影响的行行的副本本。当执执行INSERT或UPDATE操作时，新行被被同时添添加到:NEW

7、变量和触触发表中中，:NEW变量中的的行即为为触发表表中新行行的副本本。9.1触发器概概述:OLD变量和:NEW变量课堂案例例1使用SQLDeveloper管理触发发器学习使用用SQLDeveloper创建触发发器的方方法和基基本步骤骤，掌握握触发器器的执行行时机。案例学习习目标SQLDeveloper创建触发发器、验验证触发发器的作作用。案例知识识要点课堂案例例1使用SQLDeveloper管理触发发器案例完成成步骤-创建触发发器添加标题题文字1创建触触发器（1）在SQLDeveloper中右击Triggers选项，从从快捷菜菜单中选选择“NewTrigger”项，将开开始创建建触发器器。（

8、2）在打开开的“CreateTrigger”对话框中中，输入入TR_ADDUSER作为新建建触发器器的名称称；选择择触发器器类型（Trigger Type）为TABLE，并选择择触发表表为客户户表USERS。然后选选择触发发器类别别信息为为After、语句级级别（StatementLevel）和插入入型（Insert）；其余余采用默默认值。教师演示示讲解课堂案例例1使用SQLDeveloper管理触发发器案例完成成步骤-创建触发发器添加标题题文字（3）触发器器设置完完成后，单击“确定”按钮，在PL/SQL编辑窗口口中补充充创建触触发器的的PL/SQL代码。（4）触发器器编译成成功后，在PL/S

9、QL编辑窗口口中输入入以下PL/SQL代码：INSERTINTOUsersVALUES(00,trigger,普通,trigger);（5）按F9键执行该该PL/SQL语句，激激发触发发器的执执行教师演示示讲解课堂案例例1使用SQLDeveloper管理触发发器案例完成成步骤-查看触发发器添加标题题文字在SQLDeveloper的左边的的树型结结构中，展开Triggers节点后选选择需要要查看的的触发器器tr_AddUser，将在右右边栏内内显示tr_AddUser触发器的的信息，如触发发器的定定义语句句和详细细描述等等教师演示示讲解课堂案例例1使用SQLDeveloper管理触发发器案例完成

10、成步骤-修改触发发器添加标题题文字（1）在SQLDeveloper中，右键键单击Triggers选项中的的触发器器tr_AddUser，从快捷捷菜单中中选择“Edit”项。（2）在打开开的触发发器编辑辑窗口中中，修改改触发器器的定义义PL/SQL语句教师演示示讲解课堂案例例1使用SQLDeveloper管理触发发器案例完成成步骤-删除触发发器添加标题题文字（1）在SQLDeveloper中，右击击Triggers选项中的的待删除除触发器器tr_AddUser，从快捷捷菜单中中选择“Drop Trigger”项。（2）在打开开的“Drop Trigger”对话框中中，在Prompts（提示）选项

11、卡卡内显示示了待删删除触发发器的所所有者和和名称，并提示示用户是是否需要要删除该该触发器器。（3）单击“应用”按钮，将打开开Confirmation对话框，以提示示用户该该触发器器已经被被删除。教师演示示讲解课堂案例例2使用PL/SQL管理触发发器学习使用用PL/SQL语句中的的CREATETRIGGER语句的用用法。案例学习习目标CREATETRIGGER创建触发发器、验验证触发发器的作作用。案例知识识要点课堂案例例2使用PL/SQL管理触发发器案例完成成步骤-创建触发发器添加标题题文字1创建触触发器【例2-1】为用户方方案SCOTT的客户表表CUSTOMERS创建插入入型触发发器，当当添加

12、新新客户信信息时，显示“欢迎新新会员注注册！”。INSERTINTOSCOTT.CustomersVALUES(C2002,trigger,触发器,女,to_date(1999-04-14,yyyy-mm-dd)湖南长沙沙市,4100010731-8888888,123456,6666,你出生在在哪里,湖南长沙沙,普通);教师演示示讲解课堂案例例2使使用PL/SQL管理理触发器器案例完成成步骤-创建触发发器添加标题题文字【例2-2】为用户方方案SCOTT的客户表表CUSTOMERS创建删除除型触发发器，当当删除会会员信息息时，显

13、显示“用户已被被删除！”的信信息。CREATEORREPLACETRIGGER tr_DelCUSTBEFOREDELETEONSCOTT.CUSTOMERSFOREACHROWBEGINDBMS_OUTPUT.PUT_LINE(待删除的的记录为为:);DBMS_OUTPUT.PUT_LINE(:OLD.c_NAME|用户已被被删除!);ENDtr_DelCUST;DELETECUSTOMERSWHEREc_D=C2002;教师演示示讲解课堂案例例2使用PL/SQL管理触发发器案例完成成步骤-创建触发发器添加标题题文字【例2-3】实现eBuy电子商城城系统的的日志操操作功能能（语句句级触发发器

14、）。（1）创建语语句级触触发器的的测试表表(该语句应应该在创创建触发发器的语语句之前前运行)CREATETABLESYSLOG(WHOVARCHAR2(30),OPER_DATEDATE);该表用来来保存对对eBuy系统进行行操作的的日志。（2）创建语语句级触触发器CREATEORREPLACETRIGGERtr_DeleteUserAFTERDELETEONUsersBEGININSERTINTOSYSLOG(WHO,OPER_DATE)VALUES(USER,SYSDATE);END;教师演示示讲解课堂案例例2使用PL/SQL管理触发发器案例完成成步骤-创建触发发器添加标题题文字该触发器器

15、用来实实现在Users表中删除除一条记记录时，在SYSLOG表中添加加一条包包含当前前操作用用户（USER表示当前前用户）和当前前操作日日期（SYSDATE表示当前前日期）的记录录。（3）测试触触发器tr_DeleteUser通过删除除Users表中的一一条记录录（用户户名称为为“存储储过程”）来测测试触发发器的功功能。DELETEFROMUsersWHEREu_Name=存储过程程;SELECT*FROM Syslog;教师演示示讲解课堂案例例2使用PL/SQL管理触发发器案例完成成步骤-创建触发发器添加标题题文字【例2-4】通过临时时表将Users表中删除除的记录录进行临临时保存存（行级级

16、触发器器）。（1）创建行行级触发发器的测测试表CREATETABLEuserdelASSELECT*FROM UsersWHEREu_Name=amy;（2）创建行行级触发发器CREATEORREPLACETRIGGER tr_DeleteUser2AFTERDELETEONUsersFOREACHROWBEGININSERTINTOuserdelALUES(:OLD.u_ID,:OLD.u_Name,:OLD.u_Type,:OLD.u_Password);END;（3）测试触触发器tr_DeleteUser和tr_DeleteUser2DELETEFROMUsersWHEREu_Name=

17、amy;SELECT*FROM userdel;SELECT*FROM syslog;教师演示示讲解课堂案例例2使用PL/SQL管理触发发器案例完成成步骤-查看触发发器添加标题题文字【例2-5】查看用户户方案SCOTT中所有触触发器的的名称、触发类类型、触触发事件件、所有有者和触触发表等等信息。SELECTTRIGGER_NAME触发器名名,TRIGGER_TYPE触发类型型,TRIGGERING_EVENT触发事件件,TABLE_NAME触发表FROMUSER_TRIGGERS;教师演示示讲解课堂案例例2使用PL/SQL管理触发发器案例完成成步骤-修改和删删除触发发器添加标题题文字3修改触触

18、发器修改触发发器和修修改视图图类似，虽然Oracle也提供ALTERTRIGGER命令，但但它只用用于重新新编译或或者验证证现有触触发器。如果需需要修改改触发器器的定义义，则仍仍然使用用CREATEORREPLACETRIGGER命令。4删除触触发器Oracle的PL/SQL语句提供供了DROP TRIGGER命令来删删除触发发器，其其基本语语法格式式为：DROP TRIGGER用户方案案.触发器名名;【例2-6】删除用户户方案SCOTT的触发器器tr_ADDCUST。实现该删删除任务务的DROP TRIGGER命令如下下：DROP TRIGGERSCOTT. tr_ADDCUST;教师演示示

19、讲解9.3其他类型型触发器器替代触发发器添加标题题文字INSTEAD OF触发器主主要用来来对另一一个表或或者视图图进行DML操作。【例3-1】已经创建建了查询询商品信信息的视视图VW_GOODSINFO，视图结结构包含含商品编编号、商商品名称称、商品品类别和和详细描描述。对对于视图图VWGOODSINFO，考虑使使用更新新语句，以期更更新视图图，达到到更新基基表中数数据的目目的。（1）定义视视图CREATEORREPLACEVIEW SCOTT.VW_GOODSINFOASSELECTg_ID,g_NAME, t_NAME,g_PRICEFROMSCOTT.GOODS GJOINSCOTT.

20、TYPESTONG.t_ID=T.t_ID;教师演示示讲解9.3其其他类类型触发发器替代触发发器添加标题题文字（2）更新视视图数据据对于视图图VWGOODSINFO，考虑使使用更新新语句，以期更更新视图图，达到到更新基基表中数数据的目目的。相相应的PL/SQL语句如下下所示：UPDATESCOTT.VW_GOODSINFOSETg_NAME=三星SGH-C888,t_NAME =电脑产品品WHEREg_ID= 010006;教师演示示讲解9.3其其他类类型触发发器替代触发发器添加标题题文字（3）创建INSTEAD OF触发器教师演示示讲解9.3其其他类类型触发发器系统事件件触发器器添加标题题文

21、字系统事件件触发器器是指由由数据库库系统事事件触发发的数据据库触发发器。数数据库系系统事件件通常包包括以下下几种：数据库的的启动（STARTUP）；数据库的的关闭（SHUTDOWN）；数据库服服务器出出错（SERVERERROR）。【例3-2】创建数据据库启动动后记录录启动时时间的系系统事件件触发器器。（1）创建系系统事件件触发器器测试表表CREATETABLEdblog(op_datetimestamp);（2）创建系系统事件件触发器器CREATEORREPLACETRIGGER tr_StartDBAFTERSTARTUPONDATABASEBEGININSERTINTODBLOGVALU

22、ES(SYSDATE);END;教师演示示讲解9.3其其他类类型触发发器用户事件件触发器器添加标题题文字用户事件件触发器器指与数数据库定定义语句句或用户户的登录录/注销等事事件相关关的触发发器。这这些事件件包括以以下语句句，并且且可以规规定触发发时间BEFORE或AFTER。【例3-3】创建保存存系统操操作日志志的用户户事件触触发器。createorreplaceTRIGGER SCOTT.TR_DDLAFTERLOGONONSCHEMABEGININSERTINTOSCOTT.SYSLOGVALUES(USER,SYSDATE);END;教师演示示讲解9.4eBuy中的典型型触发器器插入型触触发器添加标题题文字【例4-1】在用户方方案SCOTT的商品表表GOODS中插入数数据记录录时需要要进行如如下检查查：当试试图往GOODS表中插入入在商品品类别表表TYPES中并不存存在的商商品类别别编号t_ID时，返回回一条类类别