GDOU-B-11-112
广东海洋大学学生实验报告书
实验名称 实验七：使用触发器加强完整性 课程名称 数据库原理与应用 学院（系） 学生姓名
成绩
学号
专业
实验地点
班级
实验日期
一、 实验目的 1. 掌握after和Instead of触发器的使用方法 二、实验内容 按要求完成给出的下列题目，要求写出相应数据库的脚本语句。（要求写出存储过程的创建和执行语句。） 1. 创建触发器，当修改Orders表中某行数据的mShippingCharges和mGiftWrapCharges时，要修改相应行的总价mTotalCost，以保证结果的正确性。 2. 创建after触发器，禁止删除ToysBrand表中的记录。 3. 创建Instead of触发器，禁止删除ToysBrand表中的记录。 4. 创建触发器，在Toys表中插入数据或者修改数据时，要保证siLowerAge小于siUpperAge的值，否则不能插入或修改。 5. 创建触发器，禁止对Shoppers表进行任何更新（增删改）操作。 6. 当Toys表中的玩具价格mToyRate增加时，创建一个触发器使得Toys表的mToyRate的属性的平均值不超过28美元，并且最大值不超过55美元，否则不予修改。 7. 对Shipment表进行插入操作或者修改操作时，应保证dShipmentDate小于当前日期。 8. 对于Recruitment数据库，创建一个视图名为vwCandidateContractRecruiter，其中包含了外部候选人（ExternalCandidate表）的代码，姓名，测试成绩，以及其对应的合同招聘人员（ContractRecruiter表）的代码和名称。并对该视图进行更新，修改外部候选人代码为‘000049’的候选人，更改其测验成绩为87分，并更改相对应的合同招聘人员的名称为‘Roger Federal’。通过创建一个触发器使得能对该视图直接通过下面的Update语句进行更新。 update vwCandidateContractRecruiter set siTestScore=87, cName=’Roger Federal’ where cCandidateCode=’000049’ 三、实验过程 1. CREATE TRIGGER tri1 ON Orders AFTER UPDATE AS 2. CREATE TRIGGER tri2 ON ToyBrand AFTER DELETE AS 3. CREATE TRIGGER tri3 ON ToyBrand INSTEAD OF DELETE AS 4. CREATE TRIGGER tri4_3 ON Toys AFTER UPDATE,INSERT AS 5. CREATE TRIGGER tri5 DECLARE @lage3 SMALLINT DECLARE @uage3 SMALLINT SELECT @lage3=siLowerAge FROM inserted SELECT @uage3=siUpperAge FROM inserted IF(@lage3>=@uage3) BEGIN PRINT 'Can not update or insert' ROLLBACK TRAN PRINT 'Can not delete' ROLLBACK TRANSACTION IF UPDATE (mShippingCharges) OR UPDATE (mGiftWrapCharges) BEGIN DECLARE @change1 MONEY DECLARE @change2 MONEY SELECT @change1=mShippingCharges+mGiftWrapCharges FROM inserted SELECT @change2=mShippingCharges+mGiftWrapCharges FROM deleted UPDATE Orders SET mTotalCost=mTotalCost+(@change1-@change2) WHERE cOrderNo=(SELECT cOrderNo FROM INSERTED) END END ON Shopper INSTEAD OF INSERT,DELETE,UPDATE AS 6. CREATE TRIGGER tri6 ON Toys AFTER UPDATE AS 7. CREATE TRIGGER tri7 On Shipment AFTER INSERT,UPDATE AS 8. --创建视图 DECLARE @date DATE DECLARE @curdate DATE DECLARE @redate INT SELECT @date=dShipmentDate FROM inserted SELECT @curdate=GETDATE() SELECT @redate=DATEDIFF(DD,@date,@curdate) IF @redate<0 BEGIN PRINT 'Can not insert or update!' ROLLBACK TRAN DECLARE @avgrate MONEY DECLARE @maxrate MONEY SELECT @avgrate=AVG(mToyRate) FROM Toys SELECT @maxrate=MAX(mToyRate) FROM Toys IF @avgrate>28 OR @maxrate>55 BEGIN PRINT 'Can not update!' ROLLBACK TRAN PRINT 'Can not insert,delete and update!' END END CREATE VIEW vwCandidateContractRecruiter( AS SELECT ExternalCandidate.cCandidateCode,vFirstName,vLastName,siTestScore, ContractRecruiter.cContractRecruiterCode,cName FROM ContractRecruiter,ExternalCandidate WHERE cCandidateCode,vFirstName,vLastName,siTestScore, cContractRecruiterCode,cName) ContractRecruiter.cContractRecruiterCode=ExternalCandidate.cContractRecruiterCode --创建触发器 CREATE TRIGGER tri8 On vwCandidateContractRecruiter INSTEAD OF UPDATE AS DECLARE @ccode CHAR(6) DECLARE @testscore SMALLINT DECLARE @crcode CHAR(4) DECLARE @name CHAR(35) SELECT @ccode=cCandidateCode FROM inserted SELECT @testscore=siTestScore FROM inserted SELECT @crcode=cContractRecruiterCode FROM inserted SELECT @name=cName FROM inserted UPDATE ExternalCandidate SET cCandidateCode=@ccode, siTestScore=@testscore WHERE cCandidateCode=@ccode UPDATE ContractRecruiter SET cName=@name WHERE cContractRecruiterCode=@crcode 四、实验总结 通过这次试验,我基本掌握了SQL Server的after和Instead of触发器的使用方法，这次实验对我以后的学习提供了很大的帮助，让我熟悉了数据库实现简单查询。 指导教师
日期
注：请用A4纸书写，不够另附纸。
第 页，共 页