sql server 2005事务处理

Question

大家好,有个问题请教一下:
以下的SQL Server 2005语句:
Insert into score_total(oldernumber,score_sort,score_date) values('100200','入院','20110908')
declare @total_id int
select @total_id=@@identity
insert into score_detail (score_id,scoreProject_sort_id,reference_id,acture_score) values(@total_id,1,1,0)
insert into score_detail (score_id,scoreProject_sort_id,reference_id,acture_score) values(@total_id,1,1,2)
如果我在acture_score 字段中设置约束为:acture_score<2
执行第三条语句时:违反上面的约束,那我该如何让这三条语句通过事务.回滚回去,这三条记录要么全部执行,要么全都不执行. 

Answer 1

declare @total_id int
BEGIN TRANSACTION
Insert into score_total(oldernumber,score_sort,score_date) values('100200','入院','20110908')
select @total_id=@@identity
insert into score_detail (score_id,scoreProject_sort_id,reference_id,acture_score) values(@total_id,1,1,0)
insert into score_detail (score_id,scoreProject_sort_id,reference_id,acture_score) values(@total_id,1,1,2)
commit

Answer 2

严格一点:
declare @total_id int
BEGIN TRANSACTION
Insert into score_total(oldernumber,score_sort,score_date) values('100200','入院','20110908')
select @total_id=@@identity
insert into score_detail (score_id,scoreProject_sort_id,reference_id,acture_score) values(@total_id,1,1,0)
insert into score_detail (score_id,scoreProject_sort_id,reference_id,acture_score) values(@total_id,1,1,2)
IF (@@error <> 0)   
   ROLLBACK TRANSACTION   
ELSE   
   COMMIT TRANSACTION   

Answer 3

declare @total_id int
BEGIN TRANSACTION
set XACT_ABORT ON
Insert into score_total(oldernumber,score_sort,score_date) values('100200','入院','20110908')
select @total_id=@@identity
insert into score_detail (score_id,scoreProject_sort_id,reference_id,acture_score) values(@total_id,1,1,0)
insert into score_detail (score_id,scoreProject_sort_id,reference_id,acture_score) values(@total_id,1,1,2)
commit

Answer 4

/*********************************************************************************** 整理人：中国风(Roy)  在这里举3种在SQL2005的事务写法
************************************************************************************/  use test
go
create table t (ID int)go
begin try
begin tran 
insert T values(1)
insert T values('a')
commit tran
end try
begin catch 
select ERROR_MESSAGE() AS ErrorMessage;
rollback tran 
end catch
gobegin tran
begin try
insert T values(1)
insert T values('a')
end try
begin catch
select ERROR_MESSAGE() AS ErrorMessage;
if @@trancount>0
rollback tran
end catch
if @@trancount>0
commit tran
goset xact_abort on ;
begin try
begin tran
insert T values(1)
insert T values('a')
commit tran
end try
begin catch
select ERROR_MESSAGE() AS ErrorMessage;
if (xact_state())=-1
rollback tran
if (xact_state())=1
commit tran
end catch
go
select * from T

Answer 5

sql 2005起有try.. catch..begin try
 begin tran
 Insert into score_total(oldernumber,score_sort,score_date) values('100200','入院','20110908')
 declare @total_id int
 select @total_id=@@identity
 insert into score_detail (score_id,scoreProject_sort_id,reference_id,acture_score) values(@total_id,1,1,0)
 insert into score_detail (score_id,scoreProject_sort_id,reference_id,acture_score) values(@total_id,1,1,2)
 commit tran
end try
begin catch
 rollback tran
end catch

Answer 6

declare @total_id int
set XACT_ABORT ON
Insert into score_total(oldernumber,score_sort,score_date) values('100200','入院','20110908')
select @total_id=@@identity
insert into score_detail (score_id,scoreProject_sort_id,reference_id,acture_score) values(@total_id,1,1,0)
insert into score_detail (score_id,scoreProject_sort_id,reference_id,acture_score) values(@total_id,1,1,2)

Answer 7

BEGIN TRY
BEGIN TRAN
declare @total_id int
set XACT_ABORT ON
Insert into score_total(oldernumber,score_sort,score_date) values('100200','入院','20110908')
select @total_id=@@identity
insert into score_detail (score_id,scoreProject_sort_id,reference_id,acture_score) values(@total_id,1,1,0)
insert into score_detail (score_id,scoreProject_sort_id,reference_id,acture_score) values(@total_id,1,1,2) COMMIT TRAN
END TRY
BEGIN CATCH
IF @@error<>0
ROLLBACK TRAN
SELECT ERROR_LINE(),ERROR_MESSAGE(),ERROR_STATE()
END CATCH