实验11：使用触发器加强完整性.doc

GDOU-B-11-112
广东海洋大学学生实验报告书
实验名称
实验11：使用触发器加强完整性
课程名称
数据库原理及应用
成绩

学院（系）
软件学院
专业
软件工程
班级

学生姓名

学号

实验地点

实验日期

实验目的：
1. 掌握after和Instead of触发器的使用方法
实验内容

1. 创建触发器，当修改Orders表中某行数据的mShippingCharges和mGiftWrapCharges时，要修改相应行的总价mTotalCost，以保证结果的正确性。
CREATE TRIGGER trigTotalCost ON Orders AFTER UPDATE AS
BEGIN
IF (UPDATE(mShippingCharges) OR UPDATE(mGiftWrapCharges))
BEGIN
DECLARE @orderId CHAR(6) = '';
DECLARE @newShippingCharges MONEY = 0;
DECLARE @newWrapCharges MONEY = 0;
SELECT @orderId = cOrderNo, @newShippingCharges = mShippingCharges,
@newWrapCharges = mGiftWrapCharges FROM INSERTED;
UPDATE Orders SET mTotalCost =
(SELECT SUM(mToyCost) FROM OrderDetail WHERE cOrderNo = @orderId) + @newShippingCharges + @newWrapCharges
WHERE cOrderNo = @orderId;
END
END

2. 创建after触发器，禁止删除ToysBrand表中的记录。
CREATE TRIGGER trigForbidDeleteBrand ON ToyBrand AFTER DELETE AS
BEGIN
PRINT 'ToyBrand 表中的记录不能被删除'
ROLLBACK TRAN
END

3. 创建Instead of触发器，禁止对Toys表进行删除操作。
CREATE TRIGGER trigForbidDeleteToy ON Toys INSTEAD OF DELETE AS
BEGIN
PRINT 'Toys 表中的记录不能被删除'
END
4. 创建触发器，在Toys表中插入数据或者修改数据时，要保证siLowerAge小于siUpperAge的值，否则不能插入或修改。
CREATE TRIGGER trigAgeCheck ON Toys AFTER INSERT, UPDATE AS
BEGIN
DECLARE @lowerAge SMALLINT = 0;
DECLARE @upperAge SMALLINT = 0;
SELECT @lowerAge = siLowerAge, @upperAge = siUpperAge FROM INSERTED;
IF NOT (@lowerAge < @upperAge)
BEGIN
PRINT 'siLowerAge 必须小于 siUpperAge';
ROLLBACK TRAN;
END
END

5. 创建触发器，禁止对Shoppers表进行任何更新（增删改）操作。
CREATE TRIGGER trigForbidShopperChange ON Shopper INSTEAD OF INSERT, UPDATE, DELETE AS
BEGIN
PRINT '不能对 Shopper 表进行增删改操作'
END

6. 当Toys表中的玩具价格mToyRate增加时，创建一个触发器使得Toys表的mToyRate的属性的平均值不超过28美元，并且最大值不超过55美元。
CREATE TRIGGER trigToyRate ON Toys AFTER UPDATE AS
BEGIN
IF UPDATE(mToyRate)
BEGIN
DECLARE @avgMoney MONEY = 0;
DECLARE @maxMoney MONEY = 0;
SELECT @avgMoney = AVG(mToyRate), @maxMoney = MAX(mToyRate) FROM Toys;
IF (@avgMoney > 28 OR @maxMoney > 55)
BEGIN
PRINT '平均值不能超过28美元，最大值不能超过55美元'
ROLLBACK TRAN
END
END
END

7. 对Shipment表进行插入操作或者修改操作时，应保证dShipmentDate小于当前日期。
CREATE TRIGGER trigShipmentDate ON Shipment AFTER INSERT, UPDATE AS
BEGIN
DECLARE @curDate DATE = GETDATE();
DECLARE @newDate DATE;
SELECT @newDate = dShipmentDate FROM INSERTED;
IF (@newDate >= @curDate)
BEGIN
PRINT 'dShipmentDate 必须小于当前日期';
ROLLBACK TRAN;
END
END

8. Toys表和Toybrand表存在相同属性cBrandid，利用触发器实现两个表之间的限制方式的参照完整性。
CREATE TRIGGER trigToysToToyBrand ON Toys AFTER INSERT, UPDATE AS
BEGIN
DECLARE @brandId CHAR(3);
SELECT @brandId = cBrandId FROM INSERTED;
IF NOT (@brandId IN (SELECT cBrandId FROM ToyBrand))
BEGIN
PRINT @brandId + ' 在品牌表中不存在！';
ROLLBACK TRAN;
END
END

CREATE TRIGGER trigToyBrandToToys ON ToyBrand AFTER UPDATE, DELETE AS
BEGIN
DECLARE @brandId CHAR(3);
SELECT @brandId = cBrandId FROM DELETED;
IF EXISTS (SELECT cBrandId FROM Toys WHERE cBrandId = @brandId)
BEGIN
PRINT @brandId + ' 在玩具表中被引用，不能删除或者更改！';
ROLLBACK TRAN;
END
END

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

CREATE VIEW vwCandidateContractRecruiter AS
SELECT cCandidateCode, vFirstName, vLastName, siTestScore, ExternalCandidate.cContractRecruiterCode, cName
FROM ExternalCandidate, ContractRecruiter
WHERE ExternalCandidate.cContractRecruiterCode = ContractRecruiter.cContractRecruiterCode;

CREATE TRIGGER trigContract ON vwCandidateContractRecruiter
INSTEAD OF UPDATE AS
BEGIN
DECLARE @score SMALLINT = 0;
DECLARE @name CHAR(35);
DECLARE @candidateCode CHAR(6);
DECLARE @code CHAR(4);
SELECT @score = siTestScore, @name = cName,
@code = cContractRecruiterCode, @candidateCode = cCandidateCode FROM INSERTED;

UPDATE ExternalCandidate
SET siTestScore = @score
WHERE cCandidateCode = @code;

UPDATE ContractRecruiter
SET cName = @name
WHERE cContractRecruiterCode = @code;
END