SQL transaction usage begin TRAN,COMMIT use of Tran and rollback TRAN

Source: Internet
Author: User

The Begin Tran,commit Tran and rollback TRAN are available in SQL Server 2005/2008 to use transactions.

Begin TRAN Indicates the start of a transaction,

Commit Tran represents the commit transaction,

Rollback TRAN means rolling things back

EXEC Test_proc ' literary ensemble ', ' including history, geography, Politics ', ' politics ', ' a door to literature '
CREATE PROCEDURE [dbo]. [Test_proc]
@A_Name NVARCHAR,--Name of table A
@A_Remark NVARCHAR (4000),--Notes on table A
@B_Name NVARCHAR,--Name of table B
@B_Remark NVARCHAR (4000)--Notes on table B
As
BEGIN TRY
BEGIN TRAN
--Inserting data in a table
INSERT into [dbo]. A
([A_name]
, [A_remark])
VALUES
(@A_Name
, @A_Remark)
--Insert data in table B
INSERT into [dbo]. B
([a_id]
, [B_name]
, [B_remark])
VALUES
(@ @IDENTITY--Returns the last inserted identity value
, @B_Name
, @B_Remark)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
INSERT into [dbo]. [Errorlog]
([El_procedure]--Exception stored procedure name
, [El_operatetime])--reported abnormal time
VALUES
(' Test_proc '
, CONVERT (Datetime,getdate (), 20))
END CATCH

Note: 1. The function of the @ @IDENTITY is to return the last inserted identity value.
2. I add a table in rollback TRAN that specifically records the exception so that it is produced at the time of the exception and the name of the stored procedure that determines the report exception.
Original link:
Begin Tran can be understood as a new restore point.
Commit Tran commits this change since BEGIN Tran
Rollback TRAN means reverting to the last restore point

SQL transaction usage begin TRAN,COMMIT use of Tran and rollback TRAN

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.