数据库原理实验10

实验十 触发器

1 实验目的与要求

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

2 实验内容

请完成下面实验内容：

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

create trigger employeedelete on employee48 for delete as

if exists (select * from deleted) begin

if user!='dbo' begin

print'出错了！' rollback end

end

结果：

测试如下：

(2) 创建触发器，当向订单明细表添加销售明细数据时，统计该订单销售金额。要求：如果订单金额5000元及以上，则该订单中销售的所有商品按9折进行优惠处理(更新订单明细表中成交价格)，同时还应更新订单总表中该订单的订单金额。 脚本：

(1) 触发器脚本：

alter trigger OrDetailinsert on OrderDetail48 for insert as

1

declare @ordersum numeric(8,2)

declare @orderNo char(12),@productNo char(12), @tot

numeric(8,2),@jilu char(12)

declare cur_ordetail cursor for--建立插入表游标 select orderNo48,productNo48,quantity48*price48 as total from open cur_ordetail begin transaction

create table linshi --创建临时表 (

orderNo char(12) not null, total numeric(8,2) null

constraint linshipk primary key(orderNo) )

set @ordersum=0.00

fetch cur_ordetail into @orderNo,@productNo,@tot while(@@fetch_status=0) begin

if @orderNo in (select orderNo from linshi)--如果订单编号相同，

select @ordersum=sum2 --计算插入明细各个订单的总金额 from (

select orderNo48,sum(quantity48*price48) sum2 from orderDetail48 group by orderNo48 ) b

Inserted

where b.orderNo48=@orderNo

就直接更新总金额 begin

end

close cur_ordetail

else --否则就往临时表新插入数据 insert linshi values(@orderNo,@ordersum)

fetch cur_ordetail into @orderNo,@productNo,@tot

update linshi set total=@ordersum where orderno=@orderNo

end

2

deallocate cur_ordetail

declare cur_linshi cursor for --建立对临时表的游标，判断订单总金额是否>5000 select *from linshi open cur_linshi

fetch cur_linshi into @orderNo,@tot while(@@fetch_status=0) begin

if @tot>5000 --如果大于5000，怎将其所对应订单单价全部打九折 begin

update orderDetail48 set price48=0.9*price48

where orderdetail48.orderno48=@orderNo

end

fetch cur_linshi into @orderNo,@tot

end

close cur_linshi deallocate cur_linshi

update orderMaster48 --更新订单总表 set orderSum48=sum2 from (

select orderNo48,sum(quantity48*price48) sum2 from orderDetail48 group by orderNo48) b

where orderMaster48.orderNo48=b.orderNo48 drop table linshi --删除临时表 commit tran 原图：

--提交事务

明细表

插入语句：

主表

Insert orderDetail48 values('200801090002','P20060003','5','1080.00')

3

其订单金额大于5000，可以看到20080109002这个订单所有的price均打了九折

结果：

变化后明细表

变化后主表

(3) 创建触发器，要求当修改Employee表中员工的出生日期或雇佣日期时，必须保证出生日期在雇佣日期之前，且雇佣日期与出生日期之间必须间隔16周年及以上。

脚本：

create trigger employupt on employee48 for update as

if update(birthday48) or update(hiredate48) begin

declare @birthday datetime,@hiredate datetime

select @birthday=birthday48,@hiredate=hiredate48 from deleted if year(@hiredate)-year(@birthday)<16 rollback else begin

select @birthday=birthday48,@hiredate=hiredate48 from inserted

update employee48

set birthday48=@birthday,hiredate48=@hiredate from employee48 a,inserted b

where a.employeeno48=b.employeeno48

end

End

测试：

4

update employee48 set hiredate48=1993-3-28 from employee48

where employeeno48='E2005001'

结果：

测试二：

update employee48

set hiredate48='1984-3-28' from employee48

where employeeno48='E2005002' 结果：

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

OrderMaster表中的customerNo列的值。 脚本：

alter trigger cusupt on customer48 for update as

if update(customerno48) begin

declare @customerno1 char(9),@customerno2 char(9),@row int declare cus_cur cursor for

select a.customerno48,b.customerno48 from deleted a,inserted b

select @row=count(*)from deleted if @row>1

rollback begin else

open cus_cur

fetch cus_cur into @customerno1,@customerno2 while(@@fetch_status=0) begin

update customer48

update ordermaster48

set customerno48=@customerno2

set customerno48=@customerno2

where customerno48=@customerno1

5

本文来源：https://www.bwwdw.com/article/q7wf.html