ex12触发器答案.docVIP

实验十二 触发器 请完成下面实验内容： 创建触发器，该触发器仅允许“dbo”用户可以删除Employee表内数据。 create trigger EmploteeDelete on employee for delete as if exists (select * from deleted) begin if user!=dbo begin print 该用户无权删除！ rollback end end 测试：（以sa用户） delete from employee where employeeno=E2005001 测试：（以其他用户） sp_addlogin star,star$sjj0482 sp_adduser star,star GRANT select,insert,delete,update ON employee TO star delete from employee where employeeno=E2006001  (2) 创建触发器，当向订单明细表添加销售明细数据时，统计该订单销售金额。要求：如果订单金额5000元及以上，则该订单中销售的所有商品按9折进行优惠处理(更新订单明细表中成交价格)，同时还应更新订单总表中该订单的订单金额。 create trigger orderdetailIns on orderdetail for insert as begin declare @orderno char(12),@sum int declare mycur cursor for select distinct orderNo from inserted open mycur fetch mycur into @orderno while(@@fetch_status=0) begin select @sum=sum(quantity*price) from orderdetail where orderNo=@orderno if @sum=5000 begin update orderdetail set price=price*0.9 where orderNo=@orderno update ordermaster set ordersum=@sum*0.9 where orderNo=@orderno end fetch mycur into @orderno end close mycur deallocate mycur end 测试： insert into orderdetail values(200801090001,1,900.00) select a.*,b.*,c.orderno,c.ordersum from product a,orderdetail b,ordermaster c where a.productno=b.productno and b.orderno=c.orderno and c.orderno=200801090001 select b.orderno 订单编号,ordersum 订单金额,Price 成交价格,productPrice 商品定价 from product a,ordermaster b,orderdetail c where a.productno=c.productno and b.orderno=c.orderno and c.orderno=200801090001 (3) 创建触发器，要求当修改Employee表中员工的出生日期或雇佣日期时，必须保证出生日期在雇佣日期之前，且雇佣日期与出生日期之间必须间隔周年及以上。 create trigger EmploteeUpdate on employee for update as declare @birthday datetime,@date_hired datetime if (update(birthday) or update(hiredate)) begin declare getCur cursor for select birthday,hiredate from inserted open getCur fetch getC