触发器及其应用 习题参考答案

触发器及其应用习题参考答案一、基础概念题1.简述触发器的定义及核心作用。参考答案：触发器是一种特殊的存储过程，它由数据库管理系统（DBMS）在特定事件（如INSERT、UPDATE、DELETE）发生时自动触发执行，无需用户直接调用。核心作用包括：①维护数据完整性与一致性（如级联更新关联表数据）；②实现业务规则自动化（如数据校验、日志记录）；③辅助完成复杂业务逻辑（如自动计算衍生数据）。2.触发器的触发时机主要有哪几种？分别说明其适用场景。参考答案：触发时机主要分为BEFORE（事件发生前）和AFTER（事件发生后）两种。①BEFORE触发器：适用于数据校验、数据预处理场景，例如插入数据前校验字段合法性（如金额非负）、自动填充默认值等，可阻止非法数据进入数据库；②AFTER触发器：适用于数据后续处理场景，例如数据插入后记录操作日志、更新关联表统计数据等，确保触发操作基于合法数据完成。3.简述行级触发器（FOREACHROW）与语句级触发器（FOREACHSTATEMENT）的区别。参考答案：①执行频率不同：行级触发器针对触发事件影响的每一条数据都会执行一次；语句级触发器无论事件影响多少条数据，仅执行一次。②适用场景不同：行级触发器适用于需要逐行处理数据的场景（如逐行校验、逐行更新关联数据）；语句级触发器适用于整体统计、日志记录等场景（如记录某次批量更新操作的时间和操作人员）。③数据访问权限不同：行级触发器可通过:NEW（新数据）、:OLD（旧数据）访问当前行的具体数据，语句级触发器无法直接访问单条数据。4.触发器的触发事件主要包含哪些？请分别举例说明。参考答案：触发事件主要包含DML事件（数据操纵语言事件），具体为INSERT、UPDATE、DELETE三种：①INSERT事件：当向表中插入数据时触发，例如向学生表插入新学生记录时触发；②UPDATE事件：当更新表中数据时触发，例如更新员工表中员工薪资时触发；③DELETE事件：当删除表中数据时触发，例如删除订单表中过期订单时触发。二、选择题1.下列关于触发器的说法，错误的是（）A.触发器可自动触发执行，无需手动调用B.BEFORE触发器可修改:NEW中的数据C.语句级触发器可通过:OLD访问被修改的旧数据D.触发器可用于维护数据的参照完整性参考答案：C。解析：:OLD和:NEW仅适用于行级触发器，语句级触发器无法访问单条数据的旧值和新值，因此C选项错误。2.若需要在插入数据前校验字段值的合法性，应创建的触发器类型是（）A.AFTERINSERT行级触发器B.BEFOREINSERT行级触发器C.AFTERINSERT语句级触发器D.BEFOREINSERT语句级触发器参考答案：B。解析：数据校验需要逐行检查插入的每条数据，因此需使用行级触发器；且校验需在数据插入前完成，才能阻止非法数据插入，故选择BEFOREINSERT行级触发器。3.下列场景中，最适合使用AFTER触发器的是（）A.插入订单前校验客户ID是否存在B.更新商品库存前校验库存是否充足C.删除用户后删除该用户的所有关联订单D.插入员工记录时自动填充入职日期为当前日期参考答案：C。解析：A、B、D均需在事件发生前完成处理（校验或数据填充），适合使用BEFORE触发器；C选项需在用户成功删除后，再处理关联订单，确保删除操作合法完成，适合使用AFTER触发器。三、实操应用题（基于Oracle数据库）现有数据库表结构如下，完成以下触发器的创建及相关操作：1.客户表（customer）：ssn（客户身份证号，主键）、name（客户姓名）、num_accounts（客户账户数量，默认0）2.账户表（account）：acc_no（账户号，主键）、ssn（客户身份证号，外键关联customer表ssn）、balance（账户余额）3.贷款表（loan）：ssn（客户身份证号）、code（贷款编号）、loan_date（贷款日期）、amount（贷款金额）1.创建触发器trig_insert_account，要求当向account表插入新账户时，自动更新customer表中对应客户的num_accounts（账户数量）加1。参考答案：CREATEORREPLACETRIGGERtrig_insert_accountAFTERINSERT--插入账户后更新统计数据ONaccountFOREACHROW--逐行处理每条新插入的账户记录BEGINUPDATEcustomerSETnum_accounts=num_accounts+1WHEREssn=:NEW.ssn;--通过:NEW获取新账户对应的客户ssnEND;/测试语句：--插入一条新账户记录（假设客户123456789已存在）INSERTINTOaccountVALUES('333','123456789',300);--查询客户123456789的账户数量，验证是否加1SELECTnum_accountsFROMcustomerWHEREssn='123456789';2.创建触发器trig_delete_account，要求当从account表删除账户时，自动更新customer表中对应客户的num_accounts（账户数量）减1。参考答案：CREATEORREPLACETRIGGERtrig_delete_accountAFTERDELETE--删除账户后更新统计数据ONaccountFOREACHROW--逐行处理每条被删除的账户记录BEGINUPDATEcustomerSETnum_accounts=num_accounts-1WHEREssn=:OLD.ssn;--通过:OLD获取被删除账户对应的客户ssnEND;/测试语句：--删除客户123456789的账户333DELETEFROMaccountWHEREacc_no='333'ANDssn='123456789';--查询客户123456789的账户数量，验证是否减1SELECTnum_accountsFROMcustomerWHEREssn='123456789';3.创建触发器trig_update_balance，要求当更新account表中账户余额（balance）时，若新余额为负数，则将余额设为0，并向loan表插入一条贷款记录（贷款金额为余额的绝对值，贷款日期为当前日期）。参考答案：CREATEORREPLACETRIGGERtrig_update_balanceBEFOREUPDATEOFbalance--更新余额前进行校验和处理ONaccountFOREACHROW--逐行处理每条被更新的账户记录WHEN(NEW.balance<0)--触发条件：新余额为负数BEGIN--插入贷款记录，贷款金额为新余额的绝对值，日期为当前日期INSERTINTOloanVALUES(:NEW.ssn,:NEW.acc_no||'_LOAN',CURRENT_DATE,ABS(:NEW.balance));--将负数余额设为0:NEW.balance:=0;END;/测试语句：--更新账户124的余额为-50（假设账户124存在）UPDATEaccountSETbalance=-50WHEREacc_no='124';--验证账户余额是否被设为0SELECTbalanceFROMaccountWHEREacc_no='124';--验证loan表是否新增贷款记录SELECT*FROMloanWHEREssn=(SELECTssnFROMaccountWHEREacc_no='124');4.创建触发器trig_check_balance，要求在插入账户记录时，校验余额（balance）不能小于0，若小于0则抛出错误提示“账户余额不能为负数”，并阻止插入操作。参考答案：CREATEORREPLACETRIGGERtrig_check_balanceBEFOREINSERTONaccountFOREACHROWBEGINIF:NEW.balance<0THEN--抛出错误，阻止插入操作RAISE_APPLICATION_ERROR(-20001,'账户余额不能为负数');ENDIF;END;/测试语句：--尝试插入余额为-100的账户记录，应触发错误提示INSERTINTOaccountVALUES('444','123456789',-100);四、综合应用题现有订单表（orders）：order_id（订单ID，主键）、customer_id（客户ID）、order_date（下单日期）、total_amount（订单总金额）；订单明细表（order_detail）：detail_id（明细ID，主键）、order_id（订单ID，外键关联orders表order_id）、product_id（商品ID）、quantity（购买数量）、unit_price（单价）。要求创建触发器实现以下需求：当向order_detail表插入、更新或删除明细记录时，自动计算并更新orders表中对应订单的total_amount（订单总金额=各明细记录的quantity×unit_price之和）。参考答案：--1.插入明细后更新订单总金额CREATEORREPLACETRIGGERtrig_orderdetail_insertAFTERINSERTONorder_detailFOREACHROWBEGINUPDATEordersSETtotal_amount=(SELECTSUM(quantity*unit_price)FROMorder_detailWHEREorder_id=:NEW.order_id)WHEREorder_id=:NEW.order_id;END;/--2.更新明细后更新订单总金额CREATEORREPLACETRIGGERtrig_orderdetail_updateAFTERUPDATEOFquantity,unit_price--仅当数量或单价更新时触发ONorder_detailFOREACHROWBEGINUPDATEordersSETtotal_amount=(SELECTSUM(quantity*unit_price)FROMorder_detailWHEREorder_id=:NEW.order_id)WHEREorder_id=:NEW.order_id;END;/--3.删除明细后更新订单总金额CREATEORREPLACETRIGGERtrig_orderdetail_deleteAFTERDELETEONorder_detailFOREACHROWBEGINUPDATEordersSETtotal_amount=NVL((SELECTSUM(quantity*unit_price)