数据库实验9答案

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

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

3、 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%creat

4、e trigger uptProduct on Productfor updateasbegin declare oldPrice numeric(7,2),newPrice numeric(7,2) if update(productPrice) begin declare myCur cursor for select ductPrice,ductPrice from inserted a,deleted b where ductNo=ductNo and ductClass=主板 open myCur fetch myCur into n

5、ewPrice,oldPrice while(fetch_status=0) begin if newPriceoldPrice+oldPrice*0.1 rollback fetch myCur into newPrice,oldPrice end close myCur deallocate myCur endend(5) 保证商品的销售单价必须在成本价的正负10%之内插入触发器create trigger insOrderDetail on OrderDetailfor insertasbegin declare productNo char(9),price numeric(7,2)

6、declare productPrice numeric(7,2),orderNo char(12) declare myCur cursor for select orderNo,ductNo,price,productPrice from Product a,inserted b where ductNo=ductNo open myCur fetch myCur into orderNo,productNo,price,productPrice while(fetch_status=0) begin if (priceproductPrice+product

7、Price*0.1 or priceproductPrice+productPrice*0.1 or newPriceproductPrice-productPrice*0.1) update OrderDetail set price=oldPrice where orderNo=orderNo and productNo=productNo fetch myCur into orderNo,productNo,newPrice,oldPrice,productPrice end close myCur deallocate myCur endend(6) 保证员工编码的第25位与员工的雇佣

8、日期的年份一致。插入触发器create trigger insEmplotee on employeefor insertasbegin declare employeeNo char(8),hireDate datetime declare subEmployeeNo char(4) declare myCur cursor for select employeeNo,hireDate from inserted open myCur fetch myCur into employeeNo,hireDate while(fetch_status=0) begin set subEmploye

9、eNo=substring(employeeNo,2,4) if (subEmployeeNo convert(char(4),hireDate,120) delete from Employee where employeeNo=employeeNo fetch myCur into employeeNo,hireDate end close myCur deallocate myCurend更新触发器create trigger uptEmplotee on employeefor updateasbegin declare employeeNo char(8),hireDate datetime declare subEmployeeNo char(4) if (update(employeeNo) or update(hireDate) begin declare myCur cursor for select employeeNo,hireDate from inserted open myCur fetch myCur into employeeNo,hireDate while(fetch_status=0) begin set subEmployeeNo=subs