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 t

INSERT INTO t2 VALUES (1)

INSERT INTO t2 VALUES (2) /* Foreign key error */

INSERT INTO t2 VALUES (3)

if @@error<>0

    rollback tran t

COMMIT TRAN t

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