### 在超市管理数据库SuperMarket的基础上进行实验。
1、编写一个事务处理：某学生买5袋薯片，如中间出现故障则回滚事务。#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| --定义变量，用于累计事务执行过程中的错误
declare @ErrorSum int
--初始化为0，即无错误
set @ErrorSum = 0
--编写事务
begin transaction
--更新语句
update Goods set Number = Number + 5 where CategoryNO in (
select CategoryNO from Category where CategoryName like '饼干')
--累计是否有错误
set @ErrorSum=@ErrorSum+@@ERROR
--判断执行过程中是否出现故障，是的话，回滚事务，否则提交事务
if(@ErrorSum > 0)
rollback transaction
else
commit transaction
|
2、编写一个事务，当学生购买商品时，插入购买明细到SaleBill中，并修改Goods表以保持数据一致性，如中间出现故障则回滚事务。#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| create trigger tri_SaleBill_Instered on SaleBill
for insert
as
begin
begin tran
declare @ErrorSum int
set @ErrorSum = 0
declare @number int,@goodsno varchar(10)
select @number = Number,@goodsno = GoodsNO from inserted
update Goods set Number = Number + @number where GoodsNO = @goodsno
set @ErrorSum = @ErrorSum + @@ERROR
if (@ErrorSum <> 0)
rollback tran
else
commit tran
end
--测试
insert into SaleBill values('GN0011','S01','2018-06-09 00:00:00.000',5)
|
3、编写一个事务，当撤销某个学生购买明细时，删除SaleBill 中的记录，然后修改Goods表以保持数据一致性，如中间出现故障则回滚事务。#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| create trigger tri_SaleBill_Delete on SaleBill for delete
as
begin
begin tran
declare @ErrorSum int
set @ErrorSum = 0
declare @Number int
select @Number = Number from deleted
update Goods set Number = Number - @Number
set @ErrorSum = @ErrorSum + @@ERROR
if(@ErrorSum <> 0)
rollback tran
else
commit tran
end
delete from SaleBill where GoodsNO = 'GN0011' and SNO = 'S07';
|
