数据库实验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,
from inserted a,deleted b
where= and='主板'
open myCur
fetch myCur into @newPrice,@oldPrice
while(@@fetch_status=0)
begin
if @newPrice>@oldPrice+@oldPrice*
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,,price,productPrice
from Product a,inserted b
where=
open myCur
fetch myCur into @orderNo,@productNo,@price,@productPrice while(@@fetch_status=0)
begin
if(@price>@productPrice+@productPrice*or
@price<@productPrice-@productPrice*
delete from OrderDetail
where orderNo=@orderNo and productNo=@productNo fetch myCur into @orderNo,@productNo,@price,@productPrice end
close myCur
deallocate myCur
end
更新触发器
create trigger uptOrderDetail
on OrderDetail
for update
as
begin
declare @productNo char(9),@oldPrice numeric(7,2)
declare @newPrice numeric(7,2)
declare @productPrice numeric(7,2),@orderNo char(12)
if update(price)
begin
declare myCur cursor for
select,,,,productPrice
from Product a,inserted b,deleted c
where=and=
and=
open myCur
fetch myCur into
@orderNo,@productNo,@newPrice,@oldPrice,@productPrice while(@@fetch_status=0)
begin
if(@newPrice>@productPrice+@productPrice*or
@newPrice<@productPrice-@productPrice*
update OrderDetail set price=@oldPrice
where orderNo=@orderNo and productNo=@productNo fetch myCur into
@orderNo,@productNo,@newPrice,@oldPrice,@productPrice end
close myCur
deallocate myCur
end
end