SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOALTER proc.dbo.DelData(@type varchar(50),@where varchar(8000))
As
/**//* ------- 事务开始 ---------- */
BEGIN TRANSACTION tran_test
if @type ='Vendition'
begin
--备份主表(Vendition_Data)
exec('insert into cang_back.dbo.vendition_data select * from vendition_data1 '+@where) --此处会出错
end
/**//*---------- 提交事务 ------------*/
COMMIT TRANSACTION tran_test/**//*--------- 判断是否有错误 ----------*/
print @@ERROR
IF ( @@ERROR <> 0 )
BEGIN
/**//*---------- 自定义错误输出 ----------*/
RAISERROR( 'Insert data error!',16,1 )
/**//*-------- 事务回滚 --------*/
ROLLBACK TRANSACTION tran_test
select -1 as reuslt
END
/**//*------- 判断事务数是否大于0 -----------*/
IF ( @@TRANCOUNT > 0 )
BEGIN
/**//*-------- 事务回滚 --------*/
ROLLBACK TRANSACTION tran_test
select -1 as reuslt
ENDselect 1 as reuslt
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
明明exec('insert into cang_back.dbo.vendition_data select * from vendition_data1 '+@where) --此处会出错是有错误的,但为什么不会回滚事务呀?@@ERROR为0存储过程是在数据库1上面执行,要实现的效果为数据库1里的表里的数据转到数据库2
GO
SET ANSI_NULLS ON
GOALTER proc.dbo.DelData(@type varchar(50),@where varchar(8000))
As
/**//* ------- 事务开始 ---------- */
BEGIN TRANSACTION tran_test
if @type ='Vendition'
begin
--备份主表(Vendition_Data)
exec('insert into cang_back.dbo.vendition_data select * from vendition_data1 '+@where) --此处会出错
end
/**//*---------- 提交事务 ------------*/
COMMIT TRANSACTION tran_test/**//*--------- 判断是否有错误 ----------*/
print @@ERROR
IF ( @@ERROR <> 0 )
BEGIN
/**//*---------- 自定义错误输出 ----------*/
RAISERROR( 'Insert data error!',16,1 )
/**//*-------- 事务回滚 --------*/
ROLLBACK TRANSACTION tran_test
select -1 as reuslt
END
/**//*------- 判断事务数是否大于0 -----------*/
IF ( @@TRANCOUNT > 0 )
BEGIN
/**//*-------- 事务回滚 --------*/
ROLLBACK TRANSACTION tran_test
select -1 as reuslt
ENDselect 1 as reuslt
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
明明exec('insert into cang_back.dbo.vendition_data select * from vendition_data1 '+@where) --此处会出错是有错误的,但为什么不会回滚事务呀?@@ERROR为0存储过程是在数据库1上面执行,要实现的效果为数据库1里的表里的数据转到数据库2
--备份主表(Vendition_Data)
exec('insert into cang_back.dbo.vendition_data select * from vendition_data1 '+@where) --此处会出错
end exec('insert into cang_back.dbo.vendition_data select * from vendition_data1 '+@where) -->
insert into cang_back.dbo.vendition_data select * from vendition_data1 + @where;试试
if @type ='Vendition'
begin
--备份主表(Vendition_Data)
exec('insert into cang_back.dbo.vendition_data select * from vendition_data1 '+@where) --此处会出错
end
/**//*---------- 提交事务 ------------*/
if @@error<>0
rollback TRANSACTION tran_test
else
COMMIT TRANSACTION tran_test也许错误的级别不高,所以没有回滚
我猜想是你给的@where条件里不符合语法
这样是不会有问题其实我是故意将vendition_data改为vendition_data1,因为vendition_data1是不存在的,我在测试如果在用exec执行时如果发生错误人示会回滚,我要实现的效果就是,在多个exec执行时只要有行出错就回滚事务
BEGIN TRANSACTION tran_test
if @type ='Vendition'
begin
--备份主表(Vendition_Data)
exec('insert into cang_back.dbo.vendition_data select * from vendition_data1 '+@where) --此处会出错
end
/**//*---------- 提交事务 ------------*/
COMMIT TRANSACTION tran_test 从这段话,看不出在执行错误语句前对数据库有什么操作.
BEGIN TRANSACTION tran_test
if @type ='Vendition'
begin
--备份主表(Vendition_Data)
exec('insert into cang_back.dbo.vendition_data select * from vendition_data1 '+@where) --此处会出错
end /**//*--------- 判断是否有错误 ----------*/
IF ( @@ERROR <> 0 )
BEGIN
/**//*---------- 自定义错误输出 ----------*/
RAISERROR( 'Insert data error!',16,1 )
/**//*-------- 事务回滚 --------*/
ROLLBACK TRANSACTION tran_test
select -1 as reuslt
end
else
/**//*---------- 提交事务 ------------*/
COMMIT TRANSACTION tran_test
END
exec('insert into cang_back.dbo.vendition_data select * from vendition_data '+@where)
--整机表
exec('insert into cang_back.dbo.Vendition_info select * from Vendition_info where d_NO in (select d_NO from vendition_data '+@where+' and substring(d_NO,1,1)=''A'')')
exec('insert into cang_back.dbo.Vendition_Fittings_Detail select * from Vendition_Fittings_Detail where s_code in (select d_NO from vendition_data '+@where+' and substring(d_NO,1,1)=''A'')') --如果是在这里出错的话,前面两个是正确的,会执行成功,就只有这条没有执行成功
exec('insert into tb select 1')
exec('insert into tb select ''a''')
if @@error<>0
rollback TRANSACTION
else
COMMIT TRANSACTION select * from tb空结果