IF OBJECT_ID('ShiWu1')IS NOT NULL DROP TABLE ShiWu1
GO
--创建测试Table1
create table ShiWu1(
uid int identity(1,1) primary key,
username varchar(20),
userpassword varchar(20),
)
insert into shiwu1 values('takako_yang','123456');
insert into shiwu1 values('zhongzhong_chen','abcdefg');
insert into shiwu1 values('annie_cai','ABCDEFG');
IF OBJECT_ID('ShiWu2')IS NOT NULL DROP TABLE ShiWu2
GO
----创建测试Table2
create table ShiWu2(
uid int,
username varchar(20),
email varchar(30),
roles varchar(20),
primary key(uid, username),
)
insert into shiwu2 values(1,'takako_yang','[email protected]','Admin');
insert into shiwu2 values(2,'zhongzhong_chen','[email protected]','Buyer');
insert into shiwu2 values(3,'annie_cai','[email protected]','Vendor');--事务
BEGIN transaction
declare @errorSum int
set @errorSum=0
DECLARE @ERR INT
SET @ERR=0
update shiwu1 set userpassword='19861123' where username='takako_yang'
set @errorSum=@errorSum+abs(@@error) update shiwu2 set roles='hello' where username='hello' --自己作為錯誤處理
IF @@ROWCOUNT= 0
BEGIN
SET @ERR=1
set @errorSum=@errorSum+abs(@@error)+@ERR
END
if @errorSum<>0
begin print'有错误,回滚'
rollback transaction
end else
begin print'成功,提交'
commit transaction
end--Debug
select * from shiwu1
select * from shiwu2
/*uid username userpassword
----------- -------------------- --------------------
1 takako_yang 123456
2 zhongzhong_chen abcdefg
3 annie_cai ABCDEFG(影響 3 個資料列)uid username email roles
----------- -------------------- ------------------------------ --------------------
1 takako_yang [email protected] Admin
2 zhongzhong_chen [email protected] Buyer
3 annie_cai [email protected] Vendor(影響 3 個資料列)*/
GO
--创建测试Table1
create table ShiWu1(
uid int identity(1,1) primary key,
username varchar(20),
userpassword varchar(20),
)
insert into shiwu1 values('takako_yang','123456');
insert into shiwu1 values('zhongzhong_chen','abcdefg');
insert into shiwu1 values('annie_cai','ABCDEFG');
IF OBJECT_ID('ShiWu2')IS NOT NULL DROP TABLE ShiWu2
GO
----创建测试Table2
create table ShiWu2(
uid int,
username varchar(20),
email varchar(30),
roles varchar(20),
primary key(uid, username),
)
insert into shiwu2 values(1,'takako_yang','[email protected]','Admin');
insert into shiwu2 values(2,'zhongzhong_chen','[email protected]','Buyer');
insert into shiwu2 values(3,'annie_cai','[email protected]','Vendor');--事务
BEGIN transaction
declare @errorSum int
set @errorSum=0
DECLARE @ERR INT
SET @ERR=0
update shiwu1 set userpassword='19861123' where username='takako_yang'
set @errorSum=@errorSum+abs(@@error) update shiwu2 set roles='hello' where username='hello' --自己作為錯誤處理
IF @@ROWCOUNT= 0
BEGIN
SET @ERR=1
set @errorSum=@errorSum+abs(@@error)+@ERR
END
if @errorSum<>0
begin print'有错误,回滚'
rollback transaction
end else
begin print'成功,提交'
commit transaction
end--Debug
select * from shiwu1
select * from shiwu2
/*uid username userpassword
----------- -------------------- --------------------
1 takako_yang 123456
2 zhongzhong_chen abcdefg
3 annie_cai ABCDEFG(影響 3 個資料列)uid username email roles
----------- -------------------- ------------------------------ --------------------
1 takako_yang [email protected] Admin
2 zhongzhong_chen [email protected] Buyer
3 annie_cai [email protected] Vendor(影響 3 個資料列)*/
是Begin try……end try快呢?
还是用事务快呢?
update a表
if @@error<>0 or @@rowcount=0 rollback tran
update b表
if @@error<>0 or @@rowcount=0 rollback tran
commit tran
update a表 set f=1
if @@error<>0 or @@rowcount=0 begin rollback tran return end
update b表 set e=1
if @@error<>0 or @@rowcount=0 begin rollback tran return end
commit tran
用select数据来判断是否全部插入成功了(没有符合条件也算不成功)
只有全部插入成功了才commit
否则rollback