麻烦大家帮忙看下下面的代码是不是哪里写错了,插入第二条数据的时候已经出错了,但是事物并没有完全回滚,为何?还有,一般sql语句出错了就不往下执行下面的代码了,碰到这样的情况大家都是怎么处理的啊?小弟不才,望大家不吝赐教!
BEGIN TRY
BEGIN TRANSACTION
DECLARE @t TABLE (ID INT IDENTITY(1,1) PRIMARY KEY ,NAME VARCHAR(20),Num int)
INSERT INTO @t(NAME )
SELECT 'test'
INSERT INTO @t(NUM) SELECT '3asfd'
--COMMIT TRANSACTION
SELECT '操作成功!!'
end TRY
BEGIN catch
IF (XACT_STATE()) = -1
BEGIN
SELECT N'Roll back' AS 'status'
ROLLBACK TRANSACTION;
END
IF (XACT_STATE()) = 1
BEGIN
SELECT
N'Commit' AS 'status'
COMMIT TRANSACTION;
END
SELECT '操作失败!!'
end CATCH
SELECT * FROM @t
BEGIN TRY
BEGIN TRANSACTION
DECLARE @t TABLE (ID INT IDENTITY(1,1) PRIMARY KEY ,NAME VARCHAR(20),Num int)
INSERT INTO @t(NAME )
SELECT 'test'
INSERT INTO @t(NUM) SELECT '3asfd'
--COMMIT TRANSACTION
SELECT '操作成功!!'
end TRY
BEGIN catch
IF (XACT_STATE()) = -1
BEGIN
SELECT N'Roll back' AS 'status'
ROLLBACK TRANSACTION;
END
IF (XACT_STATE()) = 1
BEGIN
SELECT
N'Commit' AS 'status'
COMMIT TRANSACTION;
END
SELECT '操作失败!!'
end CATCH
SELECT * FROM @t
commit位置也需要改一下IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 't')
BEGIN
DROP TABLE t
END
GO
CREATE TABLE t
(
ID INT IDENTITY(1,1) PRIMARY KEY ,NAME VARCHAR(20),Num int
)
BEGIN TRANSACTION
BEGIN TRY
--DECLARE @t TABLE (ID INT IDENTITY(1,1) PRIMARY KEY ,NAME VARCHAR(20),Num int)
INSERT INTO t(NAME )SELECT 'test'
INSERT INTO t(NUM) SELECT '3asfd'
SELECT '操作成功!!'
COMMIT TRANSACTION
end TRY
BEGIN catch
IF (XACT_STATE()) = -1
BEGIN
SELECT N'Roll back' AS 'status'
ROLLBACK TRANSACTION
END
IF (XACT_STATE()) = 1
BEGIN
SELECT N'Commit' AS 'status'
COMMIT TRANSACTION
END
SELECT '操作失败!!'end CATCH
SELECT * FROM t