SQL Transcation的一些总结分享

复制代码 代码如下:

-- =============================================
-- Author: JKhuang
-- Create date: 12/8/2011
-- Description: Inserts data
-- =============================================
Alter PROCEDURE SPAddDataToUserInfo
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Hard code inserted data.
INSERT INTO UserInfo VALUES('JKhuang', 8);
INSERT INTO UserInfo VALUES('Jackson', 20111111);
INSERT INTO UserInfo VALUES('JKRush', 23);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
GO

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;

-- Hard code inserted data.
INSERT INTO UserInfo VALUES('JKhuang', 8);
INSERT INTO UserInfo VALUES('Jackson', 20111111);
INSERT INTO UserInfo VALUES('JKRush', 23);

COMMIT TRANSACTION

END TRY
BEGIN CATCH
PRINT 'Error in [SPAddDataToUserInfo]: ' + ERROR_MESSAGE();
ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE();
PRINT 'Rolled back successful Transactions: ' + Convert(varchar, @@TRANCOUNT);
END CATCH

END
GO

-- =============================================
-- Author: JKhuang
-- Create date: 12/8/2011
-- Description: Invokes store procedure to insert data.
-- =============================================
ALTER PROCEDURE SPMultiDataToUserInfo

AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
PRINT 'In [SPMultiDataToUserInfo] Transactions: ' + Convert(varchar, @@TRANCOUNT);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;

-- Hard code inserted data.
INSERT INTO UserInfo VALUES('Cris', 1);
EXEC SPAddDataToUserInfo
INSERT INTO UserInfo VALUES('Ada', 32);

COMMIT TRANSACTION

END TRY
BEGIN CATCH
PRINT 'Error in [SPMultiDataToUserInfo]: ' + ERROR_MESSAGE();
ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE();
PRINT 'Rolled back successful Transactions: ' + Convert(varchar, @@TRANCOUNT);
END CATCH

END
GO

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
-- Hard code inserted data.
INSERT INTO UserInfo VALUES('JKhuang', 8);
INSERT INTO UserInfo VALUES('Jackson', 20111111);
INSERT INTO UserInfo VALUES('JKRush', 23);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Error in [SPAddDataToUserInfo]: ' + ERROR_MESSAGE();
IF (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION
PRINT 'Rolled back successful in SPAddDataToUserInfo Transactions: ' + Convert(varchar, @@TRANCOUNT);
END CATCH
END
GO
-- =============================================
-- Author: JKhuang
-- Create date: 12/8/2011
-- Description: Invokes store procedure to insert data.
-- =============================================
ALTER PROCEDURE SPMultiDataToUserInfo
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
PRINT 'In [SPMultiDataToUserInfo] Transactions: ' + Convert(varchar, @@TRANCOUNT);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
-- Hard code inserted data.
INSERT INTO UserInfo VALUES('Cris', 1);
EXEC SPAddDataToUserInfo
INSERT INTO UserInfo VALUES('Ada', 32);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Error in [SPMultiDataToUserInfo]: ' + ERROR_MESSAGE();
IF (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION
PRINT 'Rolled back successful in SPMultiDataToUserInfo Transactions: ' + Convert(varchar, @@TRANCOUNT);
END CATCH
END
GO

复制代码 代码如下:

-- =============================================
-- Transaction Temp
-- =============================================
BEGIN TRY
BEGIN TRANSACTION
--
-- You code here.
--
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
-- Adds store procedure
-- Writes the error into ErrorLog table.
ROLLBACK TRANSACTION
END IF
END CATCH