This feature has been sometime in SQL Server 2005 in SQL Server 2000 you normally use @@TRANCOUNT to detect any exception but now in SQL Server 2005 you can use try catch block.
In this snippet, there are 2 INSERT statement and 1 UPDATE statement. I’m expecting when there is any failure (e.g the UPDATE statement fails) then all the preceding INSERT/UPDATE/DELETE within the TRANSACTION block will be canceled
e.g
BEGIN TRY
BEGIN TRANSACTION transABC
INSERT INTO TABLEA (col1,col2,col3) VALUES ('a','b','c')
INSERT INTO TABLEB (col1,col2,col3) VALUES ('a','b','c')
UPDATE TABLEA SET col2='abcde' WHERE col1 = 'a'
COMMIT TRANSACTION transABC
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION transABC --RollBack in case of Error
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
Fransiscus Setiawan 