实验9：使用触发器(答案)

1、GDOU-B-11-112广东海洋大学学生实验报告书实验名称实验八：使用触发器加强完整性课程名称数据库原理与设计成绩学院（系）软件学院专业计算机软件工程班级学生姓名学号实验地点实验日期实验目的：1. 掌握触发器的使用方法实验内容按要求完成给出的下列题目，要求写出相应数据库的脚本语句。(要求写出存储过程的创建和执行语句。)1. 创建触发器，当修改 Orders表中某行数据的 mShippingCharges和mGiftWrapCharges时，要修改相应行的总价mTotalCost，以保证结果的正确性。Create trigger trgupdateOrderson Ordersfor upda

2、teasif update(mShipp in gCharges) or update(mGiftWrapCharges)begindeclare old moneydeclare new moneyselect old=mShipp in gCharges+ mGiftWrapCharges from deletedselect n ew=mShipp in gCharges+ mGiftWrapCharges from in sertedupdate Ordersset mTotalCost=mTotalcost-old+ newwhere cORderNo=(select cOrderN

3、o from in serted) end2. 创建触发器，禁止删除ToysBrand表中的记录。1. Create trigger trgdeleteToyBra ndon ToyBra ndfor deleteasrollback tran3. 创建In stead of 触发器，禁止对 Toys表进行删除操作。Create trigger trgToyson Toysin stead of deleteasprint toys cannot be deleted 4. 创建触发器，在 Toys表中插入数据或者修改数据时，要保证 siLowerAge小于siUpperAge的 值，否则不能

4、插入或修改。5. 创建触发器，禁止对 Shoppers表进行任何更新(增删改)操作。6. 当Toys表中的玩具价格 mToyRate增加时，创建一个触发器使得Toys表的mToyRate的属性的平均值不超过28美元，并且最大值不超过55美元。Create trigger updateToyson Toysfor updateasdeclare avg moneydeclare max moneyselect avg=avg(mToyrate) from toysselect max=Max(mToyrate) from toysif avg28 or max55rollback tran7.

5、对Shipment表进行插入操作或者修改操作时，应保证 dShipmentDate小于当前日期。.Create trigger trgDeleteToyBra ndOn ToyBra ndFor delete,updateAsIf exists(select * from deleted where cBra ndid in (select cBra ndid from toys) Rollback tran8. Toys表和Toybrand表存在相同属性cBrandid，利用触发器实现两个表之间的限制方式的参照 完整性。create trigger trgupdateviewOn vwCa

6、ndidateC on tractRecruiterIn stead of updateAsDeclare mark intDeclare n ame varchar(50)Declare code char(6)Select mark=siTestScore from in sertedSelect n ame=cName from in sertedSelect code=cCa ndidatecode from in sertedUpdate vwCa ndidateC on tractRecruiterSet siTestScore=mark,cName=n ameWhere cCa

7、ndidateCode=code9. 对于 Recruitment 数据库，创建一个视图名为vwCandidateContractRecruiter ，其中包含了外部候选人的代码，姓名，测试成绩，以及其对应的合同招聘人员的代码和名称。并对该进行更新，修改外部候选人代码为000049的候选人，更改其测验成绩为87分，并更改相对应的合同招聘人员的名称为Roger Federal 。通过创建一个触发器使得能对该视图直接通过下面的Update语句进行更新。update vwCa ndidateC on tractRecruiterset siTestScore=87, cName= Roger Fed

8、eral where cCandidateCode= 000049日期第页，共页指导教师注：请用A4纸书写，不够另附纸。2.Create trigger trgupdateOrders on Ordersfor updateasif update(mShipp in gCharges) or update(mGiftWrapCharges)begindeclare old moneydeclare new moneyselect old=mShipp in gCharges+ mGiftWrapCharges from deleted select n ew=mShipp in gCharge

9、s+ mGiftWrapCharges from in serted update Ordersset mTotalCost=mTotalcost-old+ newwhere cORderNo=(select cOrderNo from in serted)end3. Create trigger trgdeleteToyBra ndon ToyBra ndfor deleteasrollback tran3. Create trigger trgToyson Toysin stead of deleteasprint toys cannot be deleted 4.5略6. Create

10、trigger updateToyson Toysfor updateasdeclare avg moneydeclare max moneyselect avg=avg(mToyrate) from toysselect max=Max(mToyrate) from toysif avg28 or max55rollback tran7. Create trigger trgDeleteToyBra ndOn ToyBra ndFor delete,updateAsIf exists(select * from deleted where cBra ndid in( select cBra ndid from toys) Rollback tran8. create trigger trgupdateviewOn vwCa ndidateC on tractRecruiterIn stead of updateAsDeclare mark intDeclare n ame varchar(50)Declare code char(6)Select mark=siTestScore from in sertedSe