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','触发器器','女',to_date('1999-04-14','yyyy-mm-dd'),,'湖南长长沙市市','410001',,,'amy@163.com','123456','6666','你出生生在哪哪里','湖南长长沙','普通');教师演演示讲讲解课堂案案例2—使使用PL/SQL管管理触触发器器案例完完成步步骤-创建触触发器器添加标标题文文字【例2-2】为用户户方案案SCOTT的客户户表CUSTOMERS创建删删除型型触发发器，，当删删除会会员信信息时时，显显示““××××用户已已被删删除！！”的的信息息。CREATEORREPLACETRIGGERtr_DelCUSTBEFOREDELETEONSCOTT.CUSTOMERSFOREACHROWBEGINDBMS_OUTPUT.PUT_LINE('待删除除的记记录为为:');DBMS_OUTPUT.PUT_LINE(:OLD.c_NAME||'用户已已被删删除!');ENDtr_DelCUST;DELETECUSTOMERSWHEREc_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中所有有触发发器的的名称称、触触发类类型、、触发发事件件、所所有者者和触触发表表等信信息。。SELECTTRIGGER_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='电脑产品品'WHEREg_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中的的典型触触发器删除型