实验10 触发器

实验十触发器

1 实验目的与要求

(1) 掌握触发器的创建和使用方法。

(2) 掌握游标和触发器的综合应用方法。

2 实验内容

请完成下面实验内容：

(1)创建触发器，该触发器仅允许“dbo”用户可以删除Employee表内数据。

create trigger EmployeeDelete

on Employee

for delete

as

i f exists(select*from deleted)

b egin

if user!='dbo'

begin

print'该用户无权删除'

rollback

end

end

结果为：

添加用户：

sp_addlogin'star','star$sjj0482'/*系统已经定义好的，登陆名称，密码*/ sp_adduser'star','star'

grant select,insert,delete,update

on Employee

to star

测试：

delete from employee

where employeeNo='2005005'

(2)创建触发器，当向订单明细表添加销售明细数据时，统计该订单销售金

额。要求：如果订单金额5000元及以上，则该订单中销售的所有商品按9折进行优惠处理(更新订单明细表中成交价格)，同时还应更新订单总表中该订单的订单金额。

create trigger OrderDetailInsert

on OrderDetail

for insert

as

begin

d eclar

e @order_no char(12),@insert_sum numeric(7,2)

d eclar

e @old_orderSum numeric(7,2),@new_orderSum numeric(7,2)

d eclar

e insert_cur scroll cursor for

select orderNo,sum(quantity*price)

from inserted

group by orderNo

o pen insert_cur

f etch insert_cur into @order_no,@insert_sum

w hile(@@fetch_status=0)

b egin

select @old_orderSum=sum(quantity*price)

from OrderDetail

where orderNo=@order_no

group by orderNo

set @new_orderSum=@insert_sum+@old_orderSum

if(@new_orderSum>=5000)

begin

update OrderDetail set price=productPrice*0.9

from OrderDetail a,Product b

where a.orderNo=@order_no and a.productNo=b.productNo

select @new_orderSum=sum(quantity*price)

from OrderDetail

where orderNo=@order_no

group by orderNo

update OrderMaster set orderSum=@new_orderSum

where orderNo=@order_no

fetch insert_cur into @order_no,@insert_sum

end

e nd

c lose insert_cur

d eallocat

e insert_cur

end

drop trigger OrderDetailInsert

insert OrderDetail values('200801090001','P2*******',54,65.00)

运行结果为：

输入

select*from OrderDetail where orderNo='200801090001'

结果为：

select*from OrderMaster where orderNo='200801090001'

(3)创建触发器，要求当修改Employee表中员工的出生日期或雇佣日期时，

必须保证出生日期在雇佣日期之前，且雇佣日期与出生日期之间必须间

隔16周年及以上。

create trigger Employee_update

on Employee

for update

as

i f update(birthday)or update(hireDate)

b egin

declare @birthday datetime,@hiredate datetime

declare @age int

select @birthday=birthday,@hiredate=hireDate

from inserted

set @age=(cast(convert(char(8),@hiredate,112)as int)- cast(convert(char(8),@birthday,112)as int))/365

if(not((year(@hiredate)-year(@birthday))>16

or(year(@hiredate)-year(@birthday))=16 and

month(@hiredate)>month(@birthday)

or(year(@hiredate)-year(@birthday))=16 and