SQLServer 实验十二 触发器

请完成下面实验内容：

(1) 创建触发器，该触发器仅允许“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 [dbo].[orderdetailins]

on [dbo].[OrderDetail]

for insert

as

begin

  declare @orderno char(12),@newsum numeric(7,2)

  declare orderdetailcur cursor for

  select orderno from inserted

  open orderdetailcur

  fetch next from orderdetailcur into @orderno

  while @@fetch_status=0

      begin

          declare orderdetailsumcur cursor for

          select orderno,newsum=sum(quantity*price)

          from orderdetail

          where orderno=@orderno

          group by orderno

          open orderdetailsumcur

          fetch next from  orderdetailsumcur  into @orderno,@newsum

          while @@fetch_status=0

          begin

            if  @newsum>=5000            

                update orderdetail

                set price=productprice*0.9

                from  product a,orderdetail b

                where a.productno=b.productno and b.orderno=@orderno