USE pubs
go
if exists (select * from sysobjects where name='SHOW_ERROR' and type='U')
DROP TABLE SHOW_ERROR
go
CREATE TABLE SHOW_ERROR
(
col1 smallint NOT NULL PRIMARY KEY,
col2 smallint NOT NULL
)
go
BEGIN TRANSACTION
INSERT SHOW_ERROR VALUES (1,1)
INSERT SHOW_ERROR VALUES (1,2)
INSERT SHOW_ERROR VALUES (2,2)
COMMIT TRANSACTION
go
SELECT * FROM SHOW_ERROR
go
/*
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__SHOW_ERROR__3B40CD36'. Cannot insert duplicate key in object 'SHOW_ERROR'.
The statement has been terminated.
col1 col2
------ ------
1 1
2 2
Here's a modified version of the transaction you've just seen. This example does some simple error checking using the built-in global variable @@error, and rolls back the transaction if any statement results in an error. In this example, no rows are inserted.
*/
if exists (select * from sysobjects where name='SHOW_ERROR' and type='U')
DROP TABLE SHOW_ERROR
go
CREATE TABLE SHOW_ERROR
(
col1 smallint NOT NULL PRIMARY KEY,
col2 smallint NOT NULL
)
go
BEGIN TRANSACTION
INSERT SHOW_ERROR VALUES (1,1)
if @@error <> 0 GOTO TRAN_ABORT
INSERT SHOW_ERROR VALUES (1,2)
if @@error <> 0 GOTO TRAN_ABORT
INSERT SHOW_ERROR VALUES (2,2)
if @@error <> 0 GOTO TRAN_ABORT
COMMIT TRANSACTION
-- GOTO ENDIT
TRAN_ABORT:
ROLLBACK TRANSACTION
ENDIT:
go
SELECT * FROM SHOW_ERROR
go
-- Because quite a few people have handled errors in transactions erroneously,
-- and because it can be tedious to add a check for an error after every single command, version 6.5 added a new SET statement that will abort a transaction if any error is encountered during the transaction. (There is no WHENEVER statement in
-- Transact-SQL today, although such a feature would be very useful for situations like this.)
-- Using SET XACT_ABORT ON causes the entire transaction to be aborted and
-- rolled back if any error is encountered.
-- The default setting of this is OFF, which is consistent with pre-6.5 semantics, as well as with ANSI-standard behavior.
-- By setting the XACT_ABORT ON option on, we can now rerun the example that does not do error checking, and see that no rows were inserted.
if exists (select * from sysobjects where name='SHOW_ERROR' and type='U')
DROP TABLE SHOW_ERROR
go
CREATE TABLE SHOW_ERROR
(
col1 smallint NOT NULL PRIMARY KEY,
col2 smallint NOT NULL
)
go
SET XACT_ABORT ON
BEGIN TRANSACTION
INSERT SHOW_ERROR VALUES (1,1)
INSERT SHOW_ERROR VALUES (1,2)
INSERT SHOW_ERROR VALUES (2,2)
COMMIT TRANSACTION
go
SET XACT_ABORT OFF
SELECT * FROM SHOW_ERROR
go
