开始一个显式事务，向表中插入一条数据，定义一个保存点 s1，向表中再插入一条数据，定义一个表存点s2，向表中再插入一条数据，回滚到保存点s1，向表中再插入一条数据，提交事务，查询表中数据。
begin transaction
insert into student values ('104','四四',2);
save transaction s1;
insert into student values ('105','Tina',2);
save transaction s2;
insert into student values ('106','李四',1);
rollback transaction s1;
insert into student values ('107','Rose',2);
commit 

select * from student;


参考

------解决方案--------------------
SQL code
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[csstsale_add_user]
 
@username varchar(50),  
@passwords varchar(50),
@othername varchar(50) ,
@depart varchar(50)   
  as
   DECLARE   @mdpaswd   varchar ( 34 ) 
  begin tran /*开始事务*/
  if  ((select COUNT(1) from tb_user where username=@username )>0)
  
 print '您插入的用户名系统已经存在！'
  
  else 
  
     
      select @mdpaswd=[dbo].[MD5](@passwords,32)
     insert into tb_user (username,passwords,othername,depart)
     values(@username,@mdpaswd,@othername,@depart) 
  
   
  /*如果上面操作中有一次失败了，那么回滚事务，即让两次操作都不生效*/
if @@error!=0
begin
   rollback tran
   print '新建用户失败'
end
else
/*如果没有错误，则提交事务！*/
begin
commit tran
end

------解决方案--------------------

	
create PROCEDURE [dbo].[testHuiGun]  
@id varchar(9), 
@d_name varchar(50)
 as  
begin
	begin try
	begin tran
		insert into mailss values(@id,@d_name)
		update MailKey set zhuangtai='2' where id=@id
		if(@@ROWCOUNT<=0)
		begin
			rollback;
		end
		else
		begin
			commit tran
		end
	end try
	begin catch
	rollback
	end catch
end


我这测试没有问题呀