主键冲突不能监测到,其他错误可能可以:
CREATE PROCEDURE dp_inseruser
@id char(50),
@pwd char(50),
@name char(50),
@num int
AS
BEGIN TRANSACTION
insert userinfo(id,pwd,name,num) values(@id,@pwd,@name,@num)
if @@error<>0
begin
rollback TRANSACTION
return
end
insert userlogin(id,pwd,name) values(@id,@pwd,@name)
if @@error<>0
begin
rollback TRANSACTION
return
end
COMMIT TRANSACTION
GO
CREATE PROCEDURE dp_inseruser
@id char(50),
@pwd char(50),
@name char(50),
@num int
AS
BEGIN TRANSACTION
insert userinfo(id,pwd,name,num) values(@id,@pwd,@name,@num)
if @@error<>0
begin
rollback TRANSACTION
return
end
insert userlogin(id,pwd,name) values(@id,@pwd,@name)
if @@error<>0
begin
rollback TRANSACTION
return
end
COMMIT TRANSACTION
GO
如果这样前面
BEGIN TRANSACTION TEMPNAME(取个事务名)
insert userinfo(id,pwd,name,num) values(@id,@pwd,@name,@num)
insert userlogin(id,pwd,name) values(@id,@pwd,@name)
if @@error<>0
begin
rollback TRAN TEMPNAME(该事务名)
return
end
else commit tran TEMPNAME(该事务名)
@id char(50),
@pwd char(50),
@name char(50),
@num int
ASBEGIN TRANSACTION
insert userinfo(id,pwd,name,num) values(@id,@pwd,@name,@num)
If @@error<>0
GOTO ErrHandler
insert userlogin(id,pwd,name) values(@id,@pwd,@name)
If @@error<>0
GOTO ErrHandler
COMMIT TRANSACTION
RETURN
Errhandler:
ROLLBACK TRAN
GO
好象return只能返回整型。
CREATE PROCEDURE dp_inseruser
@id char(50),
@pwd char(50),
@name char(50),
@num int
AS
BEGIN TRANSACTION
insert userinfo(id,pwd,name,num) values(@id,@pwd,@name,@num)
if @@error<>0
begin
rollback TRANSACTION
return 2
end
COMMIT TRANSACTION
GO
我测试了一下:
DECLARE @return_status int
EXEC @return_status= dp_inseruser 'a','b','c',19
SELECT 'Return Status' = @return_status
go
这里的19是主键,第一次执行时正确执行,返回0,当我再执行一遍的时候,
由于19是主键,肯定要出错的。它应该return 2才对,
但是却报:违反了 PRIMARY KEY 约束 'PK_userInfo'。不能在对象 'userInfo' 中插入重复键。语句已终止。
它好象没有执行这一句,
if @@error<>0
begin
rollback TRANSACTION
return 2
end
这是怎么一回师???
if @@error<>0
begin
rollback TRANSACTION
return 2
end