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)湖南长沙市,4100010731-8888888,amy,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