1
-- =============================================================================2
-- 標題: 事务学习3
-- 原創: takako_mu4
-- 时间: 2008-11-285
-- 地点: 昆山6
-- =============================================================================7

8

9
--创建测试Table110
if object_id('ShiWu1')is not null drop table ShiWu111
go12
create table ShiWu1(13
uid int identity(1,1) primary key,14
username varchar(20),15
userpassword varchar(20),16
)17
insert into shiwu1 values('takako_yang','123456');18
insert into shiwu1 values('zhongzhong_chen','abcdefg');19
insert into shiwu1 values('annie_cai','ABCDEFG');20

21
--创建测试Table222
if object_id('ShiWu2')is not null drop table ShiWu223
go24
create table ShiWu2(25
uid int,26
username varchar(20),27
email varchar(30),28
roles varchar(20),29
primary key(uid, username),30
)31
insert into shiwu2 values(1,'takako_yang','takako_yang@163.com','Admin');32
insert into shiwu2 values(2,'zhongzhong_chen','zhongzhong_chen@sina.com','Buyer');33
insert into shiwu2 values(3,'annie_cai','annie_cai@compal.com','Vendor');34

35
--事务 36
BEGIN transaction37
declare @errorSum int38
set @errorSum=039
DECLARE @ERR INT40
SET @ERR=041
update shiwu1 set userpassword='19861123' where username='takako_yang'42
if @@rowcount=043
begin44
set @ERR=@ERR+145
set @errorSum=@errorSum+abs(@@error)+@ERR46
end47
--if @@error<>0 or @@rowcount=0 begin rollback tran return end48

49
update shiwu2 set roles='helloooooooooooooooooooooooooooooooooo' where username='takako_yang' --自己作為錯誤處理50
IF @@ROWCOUNT= 051
BEGIN52
SET @ERR=@ERR+153
set @errorSum=@errorSum+abs(@@error)+@ERR54
END55
if @errorSum<>056
begin print'error,rollback'57
rollback transaction58
end59

60
else61
begin print'成功,提交'62
commit transaction63
end64

65
--Debug66
select * from shiwu167
select * from shiwu2
浙公网安备 33010602011771号