Oracle第章触发器操作

第9章触发器操作主编：刘志成本章学习导航本章学习导航本章学习要点（1）触发器的基础知识。（2）:OLD和:NEW变量。（3）SQLDeveloper创建、修改、查看、编译和删除触发器。（4）PL/SQL创建、修改、查看、编译和删除触发器。（5）触发器的功能及使用。（6）eBuy电子商城中的典型触发器

建议课时：8课时9.1触发器概述

触发器简介触发器是一种特殊的存储过程，它与数据表紧密联系，用于保护表中的数据，当一个定义了特定类型触发器的基表执行插入、修改或删除表中数据的操作时，将自动触发触发器中定义的操作，以实现数据的一致性和完整性。触发器拥有比数据库本身标准的功能更精细和更复杂的数据控制能力。

触发器具有以下的作用：（1）在安全性方面，触发器可以基于数据库的值使用户具有操作数据库的某种权利。

（2）在审计方面，触发器可以跟踪用户对数据库的操作。

（3）实现复杂的数据完整性规则。

（4）实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。

（5）同步实时地复制表中的数据。（6）自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。例如，如果商品的数量低于5，则立即给管理人员发送库存报警信息。9.1触发器概述

触发器类型1．触发器的功能（1）DML触发器当对表进行DML操作时触发，可以在DML操作前或操作后触发。（2）替代触发器是Oracle用来替换所使用的实际语句而执行的触发器。（3）系统触发器在Oracle数据库系统的事件（Oracle系统的启动与关闭等）中进行触发。（4）用户事件触发器指与数据库定义语句或用户的登录/注销等事件相关的触发器。2．触发事件按触发事件的不同，触发器可以分为插入型（INSERT）、更新型（UPDATE）和删除型（DELETE）触发器。对于插入型触发器，当触发器所在的表发生插入操作时，触发器将自动触发执行。对于更新型触发器，当触发器所在的表发生更新操作时，触发器将自动触发执行。同样，对于删除型触发器，当触发器所在的表发生删除操作时，触发器也将自动触发执行。

9.1触发器概述

触发器类型3．触发时间根据指定的事件和触发器执行的先后次序，触发器可以分为BEFORE型和AFTER型触发器。如果在指定的事件（INSERT、UPDATE或者DELETE）之前执行触发器，这类触发器称为BEFORE触发器；若在指定的事件之后执行触发器，则称这类触发器为AFTER触发器。4．触发级别根据触发级别的不同，触发器可以分为行触发器和语句触发器。对于行触发器，受触发事件影响的每一行都将引发触发器的执行；而对于语句触发器，触发事件只触发一次，即使有若干行受触发事件的影响，也只执行一次触发操作。

9.1触发器概述

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

9.1触发器概述

:OLD变量和:NEW变量课堂案例1—使用SQLDeveloper管理触发器

学习使用SQLDeveloper创建触发器的方法和基本步骤，掌握触发器的执行时机

。案例学习目标

SQLDeveloper创建触发器、验证触发器的作用。案例知识要点课堂案例1—使用SQLDeveloper管理触发器案例完成步骤-创建触发器添加标题文字1．创建触发器

（1）在SQLDeveloper中右击Triggers选项，从快捷菜单中选择“NewTrigger”项，将开始创建触发器。（2）在打开的“CreateTrigger”对话框中，输入TR_ADDUSER作为新建触发器的名称；选择触发器类型（TriggerType）为TABLE，并选择触发表为客户表USERS。然后选择触发器类别信息为After、语句级别（StatementLevel）和插入型（Insert）；其余采用默认值。教师演示讲解课堂案例例1—使用SQLDeveloper管理触发发器案例完成成步骤-创建触发发器添加标题题文字（3）触发器器设置完完成后，，单击““确定””按钮，，在PL/SQL编辑窗口口中补充充创建触触发器的的PL/SQL代码。（4）触发器器编译成成功后，，在PL/SQL编辑窗口口中输入入以下PL/SQL代码：INSERTINTOUsersVALUES('00','trigger','普通','trigger');（5）按F9键执行该该PL/SQL语句，激激发触发发器的执执行教师演示示讲解课堂案例例1—使用SQLDeveloper管理触发发器案例完成成步骤-查看触发发器添加标题题文字在SQLDeveloper的左边的的树型结结构中，，展开Triggers节点后选选择需要要查看的的触发器器tr_AddUser，将在右右边栏内内显示tr_AddUser触发器的的信息，，如触发发器的定定义语句句和详细细描述等等教师演示示讲解课堂案例例1—使用SQLDeveloper管理触发发器案例完成成步骤-修改触发发器添加标题题文字（1）在SQLDeveloper中，右键键单击Triggers选项中的的触发器器tr_AddUser，从快捷捷菜单中中选择““Edit”项。（2）在打开开的触发发器编辑辑窗口中中，修改改触发器器的定义义PL/SQL语句教师演示示讲解课堂案例例1—使用SQLDeveloper管理触发发器案例完成成步骤-删除触发发器添加标题题文字（1）在SQLDeveloper中，右击击Triggers选项中的的待删除除触发器器tr_AddUser，从快捷捷菜单中中选择““DropTrigger””项。（2）在打开开的“DropTrigger””对话框中中，在Prompts（提示））选项卡卡内显示示了待删删除触发发器的所所有者和和名称，，并提示示用户是是否需要要删除该该触发器器。（3）单击““应用””按钮，，将打开开Confirmation对话框，，以提示示用户该该触发器器已经被被删除。。教师演示示讲解课堂案例例2—使用PL/SQL管理触发发器学习使用用PL/SQL语句中的的CREATETRIGGER语句的用用法。案例学习习目标CREATETRIGGER创建触发发器、验验证触发发器的作作用。案例知识识要点课堂案例例2—使用PL/SQL管理触发发器案例完成成步骤-创建触发发器添加标题题文字1．创建触触发器【例2-1】】为用户方方案SCOTT的客户表表CUSTOMERS创建插入入型触发发器，当当添加新新客户信信息时，，显示““欢迎新新会员注注册！””。INSERTINTOSCOTT.CustomersVALUES('C2002','trigger','触发器','女湖南长沙沙市你出生在在哪里','湖南长沙沙','普通');教师演示示讲解课堂案例例2—使使用PL/SQL管理理触发器器案例完成成步骤-创建触发发器添加标题题文字【例2-2】】为用户方方案SCOTT的客户表表CUSTOMERS创建删除除型触发发器，当当删除会会员信息息时，显显示“×××用户已被被删除！！”的信信息。CREATEORREPLACETRIGGERtr_DelCUSTBEFOREDELETEONSCOTT.CUSTOMERSFOREACHROWBEGINDBMS_OUTPUT.PUT_LINE('待删除的的记录为为:');DBMS_OUTPUT.PUT_LINE(:OLD.c_NAME||'用户已被删除除!');ENDtr_DelCUST;DELETECUSTOMERS WHEREc_D='C2002';教师演示讲解解课堂案例2—使用PL/SQL管理触发器案例完成步骤骤-创建触发器添加标题文字字【例2-3】实现eBuy电子商城系统统的日志操作作功能（语句句级触发器））。（1）创建语句级级触发器的测测试表(该语句应该在在创建触发器器的语句之前前运行)CREATETABLESYSLOG(WHOVARCHAR2(30),OPER_DATEDATE);该表用来保存存对eBuy系统进行操作作的日志。（2）创建语句级级触发器CREATEORREPLACETRIGGERtr_DeleteUserAFTERDELETEONUsersBEGININSERTINTOSYSLOG(WHO,OPER_DATE)VALUES(USER,SYSDATE);END;教师演示讲解解课堂案例2—使用PL/SQL管理触发器案例完成步骤骤-创建触发器添加标题文字字该触发器用来来实现在Users表中删除一条条记录时，在在SYSLOG表中添加一条条包含当前操操作用户（USER表示当前用户户）和当前操操作日期（SYSDATE表示当前日期期）的记录。。（3）测试触发器器tr_DeleteUser通过删除Users表中的一条记记录（用户名名称为“存储储过程”）来来测试触发器器的功能。DELETEFROMUsersWHEREu_Name='存储过程';SELECT*FROMSyslog;教师演示讲解解课堂案例2—使用PL/SQL管理触发器案例完成步骤骤-创建触发器添加标题文字字【例2-4】通过临时表将将Users表中删除的记记录进行临时时保存（行级级触发器）。。（1）创建行级触触发器的测试试表CREATETABLEuserdelASSELECT*FROMUsersWHEREu_Name='amy';（2）创建行级触触发器CREATEORREPLACETRIGGERtr_DeleteUser2AFTERDELETEONUsersFOREACHROWBEGININSERTINTOuserdelALUES(:OLD.u_ID,:OLD.u_Name,:OLD.u_Type,:OLD.u_Password);END;（3）测试触发器器tr_DeleteUser和tr_DeleteUser2DELETEFROMUsersWHEREu_Name='amy';SELECT*FROMuserdel;SELECT*FROMsyslog;教师演示讲解解课堂案例2—使用PL/SQL管理触发器案例完成步骤骤-查看触发器添加标题文字字【例2-5】查看用户方案案SCOTT中所有触发器器的名称、触触发类型、触触发事件、所所有者和触发发表等信息。。SELECT TRIGGER_NAME触发器名,TRIGGER_TYPE触发类型,TRIGGERING_EVENT触发事件,TABLE_NAME触发表FROMUSER_TRIGGERS;教师演示讲解解课堂案例2—使用PL/SQL管理触发器案例完成步骤骤-修改和删除触触发器添加标题文字字3．修改触发器器修改触发器和和修改视图类类似，虽然Oracle也提供ALTERTRIGGER命令，但它只只用于重新编编译或者验证证现有触发器器。如果需要要修改触发器器的定义，则则仍然使用CREATEORREPLACETRIGGER命令。4．删除触发器器Oracle的PL/SQL语句提供了DROPTRIGGER命令来删除触触发器，其基基本语法格式式为：DROPTRIGGER[用户方案.]触发器名;【例2-6】删除用户方案案SCOTT的触发器tr_ADDCUST。实现该删除任任务的DROPTRIGGER命令如下：DROPTRIGGERSCOTT.tr_ADDCUST;教师演示讲解解9.3其他类型触发发器替代触发器添加标题文字字INSTEADOF触发器主要用用来对另一个个表或者视图图进行DML操作。【例3-1】已经创建了查查询商品信息息的视图VW_GOODSINFO，视图结构包包含商品编号号、商品名称称、商品类别别和详细描述述。对于视图图VWGOODSINFO，考虑使用更更新语句，以以期更新视图图，达到更新新基表中数据据的目的。（1）定义视图CREATEORREPLACEVIEWSCOTT.VW_GOODSINFOASSELECTg_ID,g_NAME,t_NAME,g_PRICEFROMSCOTT.GOODSGJOINSCOTT.TYPESTONG.t_ID=T.t_ID;教师演示讲讲解9.3其其他类型触触发器替代触发器器添加标题文文字（2）更新视图图数据对于视图VWGOODSINFO，考虑使用用更新语句句，以期更更新视图，，达到更新新基表中数数据的目的的。相应的的PL/SQL语句如下所所示：UPDATESCOTT.VW_GOODSINFOSETg_NAME='三星SGH-C888',t_NAME='电脑产品'WHERE g_ID='010006';教师演示讲讲解9.3其其他类型触触发器替代触发器器添加标题文文字（3）创建INSTEADOF触发器教师演示讲讲解9.3其其他类型触触发器系统事件触触发器添加标题文文字系统事件触触发器是指指由数据库库系统事件件触发的数数据库触发发器。数据据库系统事事件通常包包括以下几几种：数据库的启启动（STARTUP）；数据库的关关闭（SHUTDOWN）；数据库服务务器出错（（SERVERERROR）。【例3-2】创建数据库库启动后记记录启动时时间的系统统事件触发发器。（1）创建系统统事件触发发器测试表表CREATETABLEdblog(op_datetimestamp);（2）创建系统统事件触发发器CREATEORREPLACETRIGGERtr_StartDBAFTERSTARTUPONDATABASEBEGININSERTINTODBLOGVALUES(SYSDATE);END;教师演示讲讲解9.3其其他类型触触发器用户事件触触发器添加标题文文字用户事件触触发器指与与数据库定定义语句或或用户的登登录/注销等事件件相关的触触发器。这这些事件包包括以下语语句，并且且可以规定定触发时间间BEFORE或AFTER。【例3-3】创建保存系系统操作日日志的用户户事件触发发器。createorreplaceTRIGGERSCOTT.TR_DDLAFTERLOGONONSCHEMABEGININSERTINTOSCOTT.SYSLOGVALUES(USER,SYSDATE);END;教师演示讲讲解9.4eBuy中的典型触触发器插入型触发发器添加标题文文字【例4-1】在用户方案案SCOTT的商品表GOODS中插入数据据记录时需需要进行如如下检查：：当试图往往GOODS表中插入在在商品类别别表TYPES中并不存在在的商品类类别编号t_ID时，返回一一条类别错错误的消息息；否则显显示插入成成功的消息息。教师演示讲讲解9.4eBuy中中的典型触触发器删除型触发发器添加标题文文字【例4-2】当需要要删除