SET XACT_ABORT ON中如何知道异常发生

[shankaipingo]

CREATE TABLE t1 (a int PRIMARY KEY)
CREATE TABLE t2 (a int REFERENCES t1(a))
GO
INSERT INTO t1 VALUES (1)
INSERT INTO t1 VALUES (3)
INSERT INTO t1 VALUES (4)
INSERT INTO t1 VALUES (6)
GO
SET XACT_ABORT OFF
GO
BEGIN TRAN
INSERT INTO t2 VALUES (1)
INSERT INTO t2 VALUES (2) /* Foreign key error */
INSERT INTO t2 VALUES (3)
COMMIT TRAN
GO

SET XACT_ABORT ON
GO

BEGIN TRAN
INSERT INTO t2 VALUES (4)
INSERT INTO t2 VALUES (5) /* Foreign key error */
INSERT INTO t2 VALUES (6)
COMMIT TRAN
GO

/* Select shows only keys 1 and 3 added.
Key 2 insert failed and was rolled back, but
XACT_ABORT was OFF and rest of transaction
succeeded.
Key 5 insert error with XACT_ABORT ON caused
all of the second transaction to roll back. */

SELECT *
FROM t2
GO

DROP TABLE t2
DROP TABLE t1
GO
如上所示，我如何知道异常发生了，因为我要根据异常是否发生了返回不同的值啊

[-狙击手-]

if @@error <> 0
raiserror ....

[shankaipingo]

if @@error <> 0
raiserror ....这句话，　写在哪里了？commit前还是后

[shankaipingo]

CREATE TABLE t1 (a int PRIMARY KEY)
CREATE TABLE t2 (a int REFERENCES t1(a))
GO
INSERT INTO t1 VALUES (1)
INSERT INTO t1 VALUES (3)
INSERT INTO t1 VALUES (4)
INSERT INTO t1 VALUES (6)
GO
SET XACT_ABORT OFF
GO
BEGIN TRAN
INSERT INTO t2 VALUES (1)
INSERT INTO t2 VALUES (2) /* Foreign key error */
INSERT INTO t2 VALUES (3)
COMMIT TRAN
GO

SET XACT_ABORT ON
GO

BEGIN TRAN
INSERT INTO t2 VALUES (4)
INSERT INTO t2 VALUES (5) /* Foreign key error */
if(@@error<>0)
print 'error发生了'
INSERT INTO t2 VALUES (6)
if(@@error<>0)
print 'error发生了'
COMMIT TRAN

if(@@error<>0)
print 'error发生了'

GO

/* Select shows only keys 1 and 3 added.
Key 2 insert failed and was rolled back, but
XACT_ABORT was OFF and rest of transaction
succeeded.
Key 5 insert error with XACT_ABORT ON caused
all of the second transaction to roll back. */

SELECT *
FROM t2
GO

DROP TABLE t2
DROP TABLE t1
GO



居然都不打出来

[ojuju10]

[Quote=引用 3 楼 shankaipingo 的回复:]
if @@error <> 0
raiserror ....这句话，　写在哪里了？commit前还是后
[/Quote]

-----------
后面

[lhsxsh]

接分

[shankaipingo]

没有　用。写什么都不管用。不信你试

[nzperfect]

程序里去判断err.Number或try{}

[nzperfect]

加了SET XACT_ABORT ON
就别指望在sql里捕捉了.只能在程序里判断了.

如果不加SET XACT_ABORT
还可以在sql里用@@error来判断.

[shankaipingo]

哎

[cxmcxm]

@@error系统变量<>0时即有错误