Prev: Database Audit
Next: Constraint Question .. 1:N Relationship with a "Primary" Attribute
From: simon on 18 May 2010 11:40
I would like that on error, the rollback of transaction is executed.
But I don't wont to check @@error after each INSERT or some other
statement. Is it possible to check it only at the end of the
procedure?
Otherwise I'll have hundreds of IF statetements to check after each
insert or similar.

In this example the first and last record remain in the table.
Rollback is not executed, because I check @@error after last insert,
which is successfull .

thanks, Simon

CREATE TABLE [dbo].[testTable](
[rowID] [tinyint] NOT NULL,
[rowValue] [varchar](50) NOT NULL,
CONSTRAINT [PK_testTable] PRIMARY KEY CLUSTERED
([rowID] ASC))

GO

BEGIN TRANSACTION

INSERT INTO dbo.testTable ( rowID, rowValue )
VALUES ( 1, 'test1')

INSERT INTO dbo.testTable ( rowID, rowValue )
VALUES ( 1, 'test2')

INSERT INTO dbo.testTable ( rowID, rowValue )
VALUES ( 2, 'test1')

IF @@ERROR>0
ROLLBACK TRAN
ELSE
COMMIT TRAN

GO

SELECT * FROM dbo.testTable
GO
From: Plamen Ratchev on 18 May 2010 12:13
If you use SET XACT_ABORT ON on error the entire transaction is
terminated and rolled back. Also, instead of using @@ERROR you should
use TRY...CATCH. Here is generic handling code example:

SET XACT_ABORT ON;

BEGIN TRY

BEGIN TRAN

-- your code here

COMMIT TRAN

END TRY
BEGIN CATCH

IF (XACT_STATE()) = -1
BEGIN
-- open transaction but uncommittable
ROLLBACK
END
ELSE IF (XACT_STATE()) = 1
BEGIN
-- open and committable
COMMIT -- or ROLLBACK
END
ELSE
BEGIN
-- There are no open transactions
END

END CATCH

--
Plamen Ratchev
http://www.SQLStudio.com
From: --CELKO-- on 18 May 2010 13:36
The @@ERROR flag is a hold-over from the old Sybase days. The model
was a sequence of steps that set global flags after each statement was
processed (T-SQL is short of Transaction-SQL, and it viewed each
statement as a transaction).

You should use the new TRY.. CATCH syntax, as it is closer to the ANSI/
ISO Standard than other options. DB2 and Oracle both use Standard SQL;
look at the syntax at this website:

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0001027.htm

WHENEVER <exception code> <do something>;

This model is an interrupt driven model. A block of SQL/PSM begins
with local DDL, has executable code in the middle and exception
handling at the bottom. No matter where the problem occurs in the
block, we do something. No need to write the same handler over and
over.
 | 
Pages: 1
Prev: Database Audit
Next: Constraint Question .. 1:N Relationship with a "Primary" Attribute