广东海洋大学学生实验报告书
| 实验名称 | 实验八：使用触发器加强完整性 | 课程名称 | 数据库原理与设计 | 成绩 | |||||||||
| 学院（系） | 软件学院 | 专业 | 计算机软件工程 | 班级 | |||||||||
| 学生姓名 | 学号 | 实验地点 | 实验日期 | ||||||||||
1.掌握触发器的使用方法
实验内容
按要求完成给出的下列题目，要求写出相应数据库的脚本语句。（要求写出存储过程的创建和执行语句。）
1.创建触发器，当修改Orders表中某行数据的mShippingCharges和mGiftWrapCharges时，要修改相应行的总价mTotalCost，以保证结果的正确性。
Create trigger trgupdateOrders
on Orders
for update
as
if update(mShippingCharges) or update(mGiftWrapCharges)
begin
declare @old money
declare @new money
select @old=mShippingCharges+ mGiftWrapCharges from deleted
select @new=mShippingCharges+ mGiftWrapCharges from inserted
update Orders
set mTotalCost=mTotalcost-@old+@new
where cORderNo=(select cOrderNo from inserted)
end
2.创建触发器，禁止删除ToysBrand表中的记录。
1.Create trigger trgdeleteToyBrand
on ToyBrand
for delete
as
rollback tran
3.创建Instead of触发器，禁止对Toys表进行删除操作。
Create trigger trgToys
on Toys
instead of delete
as
print ‘toys cannot be deleted’
4.创建触发器，在Toys表中插入数据或者修改数据时，要保证siLowerAge小于siUpperAge的值，否则不能插入或修改。
5.创建触发器，禁止对Shoppers表进行任何更新（增删改）操作。
6.当Toys表中的玩具价格mToyRate增加时，创建一个触发器使得Toys表的mToyRate的属性的平均值不超过28美元，并且最大值不超过55美元。
Create trigger updateToys
on Toys
for update
as
declare @avg money
declare @max money
select @avg=avg(mToyrate) from toys
select @max=Max(mToyrate) from toys
if @avg>28 or @max>55
rollback tran
7.对Shipment表进行插入操作或者修改操作时，应保证dShipmentDate小于当前日期。
.Create trigger trgDeleteToyBrand
On ToyBrand
For delete,update
As
If exists(select * from deleted where cBrandid in(select cBrandid from toys))
Rollback tran
8.Toys表和Toybrand表存在相同属性cBrandid，利用触发器实现两个表之间的方式的参照完整性。
create trigger trgupdateview
On vwCandidateContractRecruiter
Instead of update
As
Declare @mark int
Declare @name varchar(50)
Declare @code char(6)
Select @mark=siTestScore from inserted
Select @name=cName from inserted
Select @code=cCandidatecode from inserted
Update vwCandidateContractRecruiter
Set siTestScore=@mark,cName=@name
Where cCandidateCode=@code
9.对于Recruitment数据库，创建一个视图名为vwCandidateContractRecruiter，其中包含了外部候选人的代码，姓名，测试成绩，以及其对应的合同招聘人员的代码和名称。并对该进行更新，修改外部候选人代码为‘000049’的候选人，更改其测验成绩为87分，并更改相对应的合同招聘人员的名称为‘Roger Federal’。通过创建一个触发器使得能对该视图直接通过下面的Update语句进行更新。
update vwCandidateContractRecruiter
set siTestScore=87, cName=’Roger Federal’
| where cCandidateCode=’000049’ |
| 指导教师 | 日期 |
2.
Create trigger trgupdateOrders
on Orders
for update
as
if update(mShippingCharges) or update(mGiftWrapCharges)
begin
declare @old money
declare @new money
select @old=mShippingCharges+ mGiftWrapCharges from deleted
select @new=mShippingCharges+ mGiftWrapCharges from inserted
update Orders
set mTotalCost=mTotalcost-@old+@new
where cORderNo=(select cOrderNo from inserted)
end
3.Create trigger trgdeleteToyBrand
on ToyBrand
for delete
as
rollback tran
3. Create trigger trgToys
on Toys
instead of delete
as
print ‘toys cannot be deleted’
4.5略
6. Create trigger updateToys
on Toys
for update
as
declare @avg money
declare @max money
select @avg=avg(mToyrate) from toys
select @max=Max(mToyrate) from toys
if @avg>28 or @max>55
rollback tran
7.Create trigger trgDeleteToyBrand
On ToyBrand
For delete,update
As
If exists(select * from deleted where cBrandid in(select cBrandid from toys))
Rollback tran
8.create trigger trgupdateview
On vwCandidateContractRecruiter
Instead of update
As
Declare @mark int
Declare @name varchar(50)
Declare @code char(6)
Select @mark=siTestScore from inserted
Select @name=cName from inserted
Select @code=cCandidatecode from inserted
Update vwCandidateContractRecruiter
Set siTestScore=@mark,cName=@name
Where cCandidateCode=@code下载本文
