begin trans
1 多个insert table1....
2 多个update table2....
3 多个delete table3.....
.......
if error<>0
rollback
else
commit trans
1 多个insert table1....
2 多个update table2....
3 多个delete table3.....
.......
if error<>0
rollback
else
commit trans
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
GOSET XACT_ABORT ON
GOBEGIN 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
GODROP TABLE t2
DROP TABLE t1
GO