存储过程中事务回滚与返回值的问题。

[bangle]

我制作了一个存储过程来实现3个表格的数据添加的操作，同时其中有2个表对应各有一个insert的触发器用作检查数据添加时某一字段是否已有重复数据。如果其中一个表格添加失败，存储过程就回滚操作并返回错误信息值。现遇到回滚语句放置的地方会出现不同的问题。代码如下：

use testDB
go
if exists(select name from sysobjects where name = 'procTest1' and type='P')
drop procedure procTest1
go
create procedure procTest1
	@menbName varchar(50)='',
	@custName varchar(50)='',
	@custAddress varchar(50)='',
	@custPhone varchar(50)='',
	@prodSerial varchar(50)='',
	@prodFactName varchar(50)='',
	@prodConfign varchar(50)='',
	@prodModel varchar(50)=''
as
begin tran
	insert into Men1(name) values(@menbName)
	insert into Cust1(custName,custAddress,custPhone) values(@custName,@custAddress,@custPhone)
	insert into Prod1(prodSerial,prodFactName,prodConfign,prodModel) values (@prodSerial,@prodFactName,@prodConfign,@prodModel)
if((select count(ID) from MsgError) > 0)
begin
	select * from MsgError
	-->rollback tran放置该位置后，MsgError表保存的错误信息被清空。
	rollback tran
	if exists(select msgStyle from MsgError where msgStyle = 'Men1')
		return 1	
	else
		return 2	

	-->rollback tran放置该位置后，执行报错。
	/**
	消息 266，级别 16，状态 2，过程 procTest1，第 20 行
	EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配。上一计数 = 0，当前计数 = 1。
	**/
end	
else		
	commit tran
	return @@error
go
if exists(select name from sysobjects where name='trigMen1Insert' and type='TR')
drop trigger trigMen1Insert
go
create trigger trigMen1Insert on Men1
instead of insert as
declare @业务员 varchar(50)
select @业务员=name from inserted
if (@业务员 in (select name from Men1))
	insert into MsgError values((select count(ID)+1 from MsgError),'成员表已存在该名字。','Men1')
else
	insert into Men1(name) values(@业务员)
go
if exists(select name from sysobjects where name = 'trigCust1Insert' and type='TR')
drop trigger trigCust1Insert
go
create trigger trigCust1Insert on Cust1
instead of insert as
declare @客户名 varchar(50)
select @客户名=custName from inserted
if (@客户名 in(select custName from Cust1))
	insert into MsgError values((select count(ID) +1 from MsgError),'客户表已存在该用户名','Cust1')
else
	insert into Cust1(custName,custAddress,custPhone) select custName,custAddress,custPhone from inserted
go
declare @megs int 
exec @megs=procTest1 '王一','客户名','客户地址','13900201243','A234553','宝鸡机床','980TDb/DA98','TK36S'
select @megs as '返回值'
select * from MsgError
go

望有能之士仗义帮忙。

[我腫了]

用if不就可以了嗎？ 先判斷是否已有該數據，是則不添加，否則添加。

[bangle]

问题已解决，是存储过程的If...Else...的流程控制上出现问题。 解决后的代码如下：

create procedure procTest1
	@menbName varchar(50)='',
	@custName varchar(50)='',
	@custAddress varchar(50)='',
	@custPhone varchar(50)='',
	@prodSerial varchar(50)='',
	@prodFactName varchar(50)='',
	@prodConfign varchar(50)='',
	@prodModel varchar(50)=''
as
begin tran
	insert into Men1(name) values(@menbName)
	insert into Cust1(custName,custAddress,custPhone) values(@custName,@custAddress,@custPhone)
	insert into Prod1(prodSerial,prodFactName,prodConfign,prodModel) values (@prodSerial,@prodFactName,@prodConfign,@prodModel)
if (select count(ID) from MsgError) > 0
begin
	select * from MsgError
	if exists(select msgStyle from MsgError where msgStyle = 'Men1')
	begin
		rollback tran
		return 1	
	end
	else
	begin
		rollback tran
		return 2	
	end
end	
commit tran
return @@error