mysql实验九_数据库实验9答案

《数据库实验9答案》由会员分享，可在线阅读，更多相关《数据库实验9答案(7页珍藏版)》请在人人文库网上搜索。

1、1) 设置一个触发器，该触发器仅允许“dbo”用户可以删除Employee表内数据，否则出错。create trigger EmploteeDelete on employeefor deleteasif exists (select * from deleted)beginif user!=dborollbackend(2) 在OrderMaster表中创建触发器，插入数据时要先检查Employee表中是否存在同样值的业务员编号，如果不存在则不允许插入。create trigger insOrderMasteron OrderMasterfor insertasbeginif exists 。

2、(select *from inserted where salerNo not in (select employeeNofrom Employee)rollback;end(3) 级联更新：当更新Customer表中customerNo列的值时，同时更新OrderMaster表中的customerNo列的值，并且一次只能更新一行。create trigger uptCustomer on Customerfor updateasdeclare oldCustomerNo char(9),newCustomerNo char(9)if (select count(*) from insert。

3、ed)1rollbackelsebeginif update(customerNo)beginselect oldCustomerNo=customerNo from deletedselect newCustomerNo=customerNo from insertedupdate OrderMaster set customerNo=newCustomerNowhere customerNo=oldCustomerNoendend(4) 对Product表写一个UPDATE触发器。当更新“主板”类的商品时，其单价不得高于原价格的10%create trigger uptProduct on。

4、 Productfor updateasbegindeclare oldPrice numeric(7,2),newPrice numeric(7,2)if update(productPrice)begindeclare myCur cursor forselect a.productPrice,b.productPricefrom inserted a,deleted bwhere a.productNo=b.productNo and b.productClass=主板open myCurfetch myCur into newPrice,oldPricewhile(fetch_stat。

5、us=0)beginif newPriceoldPrice+oldPrice*0.1rollbackfetch myCur into newPrice,oldPriceendclose myCurdeallocate myCurendend(5) 保证商品的销售单价必须在成本价的正负10%之内插入触发器create trigger insOrderDetail on OrderDetailfor insertasbegindeclare productNo char(9),price numeric(7,2)declare productPrice numeric(7,2),orderNo c。

6、har(12)declare myCur cursor forselect orderNo,b.productNo,price,productPricefrom Product a,inserted bwhere a.productNo=b.productNoopen myCurfetch myCur into orderNo,productNo,price,productPricewhile(fetch_status=0)beginif (priceproductPrice+productPrice*0.1 or priceproductPrice+productPrice*0.1 or n。

7、ewPrice convert(char(4),hireDate,120)delete from Employeewhere employeeNo=employeeNofetch myCur into employeeNo,hireDateendclose myCurdeallocate myCurend更新触发器create trigger uptEmplotee on employeefor updateasbegindeclare employeeNo char(8),hireDate datetime declare subEmployeeNo char(4)if (update(em。

8、ployeeNo) or update(hireDate)begindeclare myCur cursor forselect employeeNo,hireDatefrom insertedopen myCurfetch myCur into employeeNo,hireDatewhile(fetch_status=0)beginset subEmployeeNo=substring(employeeNo,2,4)if (subEmployeeNo convert(char(4),hireDate,120)rollbackfetch myCur into employeeNo,hireDateendclose myCurdeallocate myCurendend。