事务（插入操作）如果执行不成功就删除表里全部的数据

CREATE PROCEDURE [insert_salexm_temp1]
(@xh_1 [tinyint],
@ypID_2 [int],
@sl_3 [decimal],
@rkdj_4 [smallmoney],
@xsdj_5 [smallmoney],
@xsje_6 [money])

AS
begin
begin transaction rktd
 INSERT INTO [yd].[dbo].[salexm_temp1] ( [xh],[ypID],[sl],[rkdj],[xsdj],[xsje])  
VALUES ( @xh_1,@ypID_2,@sl_3,@rkdj_4,@xsdj_5,@xsje_6)
commit transaction rktd
if @@ERROR<>0  
delete salexm_temp1
end

如果事务rktd执行不成功，就删除表salexm_temp1里的全部数据，但每次都没起作用?


还有一个问题：
CREATE PROCEDURE [insert_sale]
(@doctorname [varchar](50),@czyname [varchar](50))
AS
BEGIN TRANSACTION tran1
INSERT INTO [yd].[dbo].[sale] ( [doctorname],[czyname]) 
VALUES ( @doctorname,@czyname)
select SCOPE_IDENTITY( ) AS djh ----这句的作用是把这条记录插入表sale后标识列产生的编号返回，我在前台调用成功
INSERT INTO [yd].[dbo].[salexm_temp1] (xsdjh) VALUES (djh)
return
现在问题是我想将返回的标识列直接在下面的INSERT INTO语句中直接使用。
结果不行，我又想用定义变量a的方法：
declare @a int
set @a=select SCOPE_IDENTITY( )
这样行吗？

直接回滚
方法1：直接写入到sql 中
在存储过程中使用 BEGIN TRANS, COMMIT TRANS, ROLLBACK TRANS 实现 
begin trans
declare @orderDetailsError int,@procuntError int
delete from [order details] where productid=42
select @orderDetailsError =@@error
delete from products where productid=42
select @procuntError=@@error
if(@orderDetailsError =0 and @procuntError=0)
COMMIT TRANS
else
ROLLBACK TRANS

SQL code

CREATE PROCEDURE [insert_salexm_temp1]
(@xh_1 [tinyint],
@ypID_2 [int],
@sl_3 [decimal],
@rkdj_4 [smallmoney],
@xsdj_5 [smallmoney],
@xsje_6 [money])

AS
begin
begin transaction rktd
 INSERT INTO [yd].[dbo].[salexm_temp1] ( [xh],[ypID],[sl],[rkdj],[xsdj],[xsje])   
VALUES ( @xh_1,@ypID_2,@sl_3,@rkdj_4,@xsdj_5,@xsje_6)
if @@ERROR<>0   
delete salexm_temp1
else
commit transaction rktd

end

+1

CREATE PROCEDURE [insert_sale]
(@doctorname [varchar](50),@czyname [varchar](50))
AS
BEGIN TRANSACTION tran1
declare @i table(id int)
INSERT INTO [yd].[dbo].[sale] ( [doctorname],[czyname])
output inserted.id into @i  
VALUES ( @doctorname,@czyname)

从@i 表变量中查刚才的标识值

老师，刚才用你的方法试了，故意让第3次插入salexm_temp1的数据出错，前2条还是没有删除。
前台是用VB写的，用for循环调用这个存储过程向salexm_temp1表里插入数据，
当前台本次FOR循环任何一次调用这个存储过程出错都必须将salexm_temp1表里的数据清空。