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                        

               

                update OrderMaster set orderSum=sum2

                from OrderMaster a,(select orderNo,sum(quantity*price) sum2

                               from OrderDetail

                               where orderNo=@orderno

                               group by orderno) b

                where a.orderNo=b.orderNo            

            fetch next from  orderdetailsumcur  into @orderno,@newsum

         end

         close  orderdetailsumcur

         deallocate orderdetailsumcur

    end

    fetch next from orderdetailcur into @orderno

    close  orderdetailcur

    deallocate orderdetailcur

end

测试：

insert into orderdetail values('200801090001','P20060003','2','1200.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表中员工的出生日期或雇佣日期时，必须保证出生日期在雇佣日期之前，且雇佣日期与出生日期之间必须间隔16周年及以上。

CREATE  FUNCTION GetAge (@birthday datetime, @today datetime)

RETURNS int AS 

BEGIN

    declare @bd int

    select @bd =(cast(convert(char( 8 ),@today,112) as int) - cast(convert(char( 8 ),@birthday,112) as int))/10000

    return @bd

END

create trigger  EmploteeInsert on employee

for insert

as

  begin

     declare @employeeno  char(8), @age int

     declare ageCur cursor for

     select employeeno,dbo.getage( birthday , hiredate) as age 

     from inserted

     open ageCur

     fetch ageCur into @employeeno, @age

     while (@@fetch_status=0)

        begin

           if  @age<16

             begin

               print '年龄太小，不能雇佣童工!'

               rollback

             end

           fetch ageCur into @employeeno, @age

        end

     close ageCur

     deallocate ageCur

  end

测试：

（1）insert Employee values('E2012001','康慨','M','19850415','南京市青海路号','13817605201', '20010416','财务科','科长',5800.80)

（2）insert Employee values('E2012002','康慨','M','19850415','南京市青海路号','13817605201', '20010413','财务科','科长',5800.80)

(3-1)创建触发器，要求当修改Employee表的员工生日或雇佣日期时必须保证出生日期在雇佣日期之前，雇佣日期与出生日期必须间隔在20年之上，且当前年龄不小于25岁。

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 getCur into @birthday, @date_hired

     while (@@fetch_status=0)

     begin

       if @date_hired<=@birthday

          rollback

        else

          if year(@date_hired)-year(@birthday) <20

               rollback

           else

             if year(getdate())-year(@birthday) <25

                rollback

        fetch getCur into @birthday, @date_hired

     end

     close getCur

     deallocate getCur

  end

测试：

update employee

set birthday='1965-01-01'

where employeeno='E2005001'

select *

from employee

where employeeno='E2005001'

（）

update employee

set birthday='1999-01-01'/*set birthday='1969-01-01'*/

where employeeno='E2005001'

select *

from employee

where employeeno='E2005001'

(4) 当更新Customer表中的customerNo列的值的时候(一次只能更新一行)，同时更新OrderMaster表中的customerNo列的值。

创建触发器：

CREATE TRIGGER cusOM_upd ON Customer

FOR UPDATE

AS

    IF UPDATE(customerNo)

    IF (SELECT count(*) FROM inserted)>1

         ROLLBACK

    ELSE

    BEGIN

        DECLARE @newcustNo char(9),@oldcustNo  char(9)

        SELECT @newcustNo=customerNo FROM inserted

        SELECT @oldcustNo=customerNo FROM deleted

        UPDATE OrderMaster

        SET customerNo=@newcustNo

        WHERE customerno=@oldcustNo

    END

测试：

UPDATE Customer

SET  customerNo='C20060003'

WHERE customerNo='C20070002'

select *

from OrderMaster

WHERE customerNo='C20060003'

(5) 创建触发器，当业务员总销售业绩超过(含)10000元时，其薪水自动增加20%，当业务员总销售业绩超过(含)5000元低于10000时，其薪水自动增加10%，当业务员总销售业绩低于5000时，其薪水自动减少10%。

create trigger insertordermaster on ordermaster

for  insert

as

begin

 if  (select count(*) from inserted)>=1

 begin

   declare @salerno char(8),@sumordersum numeric(9,2)

   declare sumemp cursor  for

   SELECT salerno,sum(ordersum)

   from ordermaster

   group by salerno

   open sumemp

   fetch sumemp into @salerno ,@sumordersum

   while(@@fetch_status=0)

   begin

       if @sumordersum>=10000

           update employee set salary=salary*1.2 from employee where employeeno=@salerno

       else

           if  @sumordersum<10000  and @sumordersum>=5000

              update employee set salary=salary*1.1  from employee where employeeno=@salerno

           else

              update employee set salary=salary*0.9  from employee   where employeeno=@salerno

       fetch sumemp into @salerno ,@sumordersum

   end

   close sumemp

   deallocate sumemp

  end

end

测试：

select employeeno,salary from employee  where employeeno='E2008003'

insert OrderMaster values('201201090003','C20050001','E2008003','20080109',5500,'I000000001') 

/*分别查询OrderMaster和employee表中相关元组的对应属性值的变化*/

(6) 创建触发器，将入职3个月均没有业绩的业务科人员做离职处理，其信息拷贝到离职表中(离职表需要自己事先建好，其表结构与员工表相同)。

CREATE TABLE leave(

  leaveNo   char(8)        not null             /*员工编号*/

     check(leaveNo like '[E][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),

  leaveName varchar(10)    not null,             /*员工姓名*/

  sex          char(1)        not null,             /*员工性别*/

  birthday     datetime       null,                 /*员工生日*/

  address      varchar(50)    null,                 /*员工住址*/

  telephone    varchar(20)    null,                 /*员工电话*/

  hireDate     datetime       not null,             /*雇佣日期*/

  department   varchar(30)    not null,             /*所属部门*/

  headShip     varchar(10)    not null,             /*职务*/

  salary       numeric(8,2)   not null,             /*薪水*/

    constraint leavePK primary key (leaveNo)

)

Go

create trigger empdelete

on employee

for delete

as

begin

   declare @employeeNo   char(8)      

   declare @employeeName varchar(10)

   declare @sex          char(1)       

   declare @birthday     datetime     

   declare @address      varchar(50)  

   declare @telephone    varchar(20) 

   declare @hireDate     datetime     

   declare @department   varchar(30) 

   declare @headShip     varchar(10)  

   declare @salary       numeric(8,2) 

   declare empdel cursor  for

   select *

   from deleted

   where department='业务科' and (getdate()-hiredate)>90 and employeeno not in (select distinct salerno

                         from  ordermaster)

   open empdel

   fetch next from empdel into @employeeNo,@employeeName,@sex,@birthday , @address,@telephone,@hireDate,@department,@headShip,@salary

   while @@fetch_status = 0

        begin

            insert into  leave values(@employeeNo,@employeeName,@sex,@birthday , @address,@telephone,@hireDate,@department,@headShip,@salary)

            fetch next from empdel into @employeeNo,@employeeName,@sex,@birthday , @address,@telephone,@hireDate,@department,@headShip,@salary

        end

   close empdel

   deallocate empdel

end

测试：

delete

from employee

where department='业务科' and (getdate()-hiredate)>90 and employeeno not in (select distinct salerno from  ordermaster)

select * from leave

/*入职满3个月的表示方法*/

select @bd =(cast(convert(char( 8 ),@hiredate,112) as int) - cast(convert(char( 8 ),getdate(),112) as int))  ---计算入职以来的时间表达式---

@hiredate  与 getdate()  同一年份  则@bd>=300   ---表示满3个月--

@hiredate  与 getdate()  同一年份  则@bd>=9100  ---表示满3个月--