sql server 2005事务处理

大家好,有个问题请教一下:
以下的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
执行第三条语句时:违反上面的约束,那我该如何让这三条语句通过事务.回滚回去,这三条记录要么全部执行,要么全都不执行. 

SQL code

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

严格一点:
SQL code

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   

SQL code

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

SQL code

/*********************************************************************************** 

整理人：中国风(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
go

begin 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
go

set 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

sql 2005起有try.. catch..
SQL code

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

SQL code

            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)

SQL code

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