数据库实验9答案
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
(1) 设置一个触发器，该触发器仅允许“dbo”用户可以删除Employee表内数据，否则出错。
create trigger EmploteeDelete on employee
for delete
as
if exists (select * from deleted)
begin
if user!='dbo'
rollback
end
(2) 在OrderMaster表中创建触发器，插入数据时要先检查Employee表中是否存在同样值的业务员编号，如果不存在则不允许插入。
create trigger insOrderMaster
on OrderMaster
for insert
as
begin
if exists(select*
from inserted
where salerNo not in
(select employeeNo
from Employee))
rollback;
end
(3) 级联更新：当更新Customer表中customerNo列的值时，同时更新OrderMaster表中的customerNo列的值，并且一次只能更新一行。
create trigger uptCustomer
on Customer
for update
as
declare @oldCustomerNo char(9),@newCustomerNo char(9)
if(select count(*)from inserted)>1
rollback
else
begin
if update(customerNo)
begin
select @oldCustomerNo=customerNo from deleted
select @newCustomerNo=customerNo from inserted
update OrderMaster set customerNo=@newCustomerNo
where customerNo=@oldCustomerNo
end
end
(4) 对Product表写一个UPDATE触发器。
当更新“主板”类的商品时，其单价不得高于原价格的10% create trigger uptProduct
on Product
for update
as
begin
declare @oldPrice numeric(7,2),@newPrice numeric(7,2)
if update(productPrice)
begin
declare myCur cursor for
select a.productPrice,b.productPrice
from inserted a,deleted b
where a.productNo=b.productNo and b.productClass='主板' open myCur
fetch myCur into @newPrice,@oldPrice
while(@@fetch_status=0)
begin
if @newPrice>@oldPrice+@oldPrice*0.1
rollback
fetch myCur into @newPrice,@oldPrice
end
close myCur
deallocate myCur
end
end
(5) 保证商品的销售单价必须在成本价的正负10%之内
插入触发器
create trigger insOrderDetail
on OrderDetail
for insert
as
begin
declare @productNo char(9),@price numeric(7,2)
declare @productPrice numeric(7,2),@orderNo char(12)
declare myCur cursor for
select orderNo,b.productNo,price,productPrice
from Product a,inserted b
where a.productNo=b.productNo
open myCur
fetch myCur into @orderNo,@productNo,@price,@productPrice while(@@fetch_status=0)
begin
if(@price>@productPrice+@productPrice*0.1 or
@price<@productPrice-@productPrice*0.1)
delete from OrderDetail
where orderNo=@orderNo and productNo=@productNo fetch myCur into @orderNo,@productNo,@price,@productPrice end
close myCur
deallocate myCur
end