Let's take a look at the "SQL Server transaction example" circulating on the Internet"

Create procedure testpro
As
/** // * ------- Start transaction ----------*/
Begin transaction tran_test

/** // * -------- Save the transaction ----------*/
Save transaction tran_test

/** // * -------- Data operation ---------*/
Insert [Table1] ([content]) values ('20140901 ')

/** // * ---------- Submit the transaction ------------*/
Commit transaction tran_test

/** // * --------- Determine whether an error exists ----------*/
If (@ error <> 0)
Begin
/** // * ---------- Custom error output ----------*/
Raiserror ('insert data error! ', 16,1)
/** // * -------- Transaction rollback --------*/
Rollback transaction tran_test
End

/** // * ------- Determine whether the number of transactions is greater than 0 -----------*/
If (@ trancount> 0)
Begin
/** // * -------- Transaction rollback --------*/
Rollback transaction tran_test
End
Go

I have also used this example of Stored Procedure transactions circulating on the Internet. But today I will take a look at whether committed transactions can be rolled back?

Intuitively, it was wrong. I thought there was another "implicit", so I did a test. The test code is as follows:

Create a table and add constraints to it:

Use [test]
Go
/***** Object: Table [DBO]. [salary] script Date: 12/12/2007 14:24:47 ******/
Set ansi_nulls on
Go
Set quoted_identifier on
Go
Create Table [DBO]. [salary] (
[ID] [int] identity (1, 1) not null,
[Salary] [real] Null
) On [primary]

Go
Alter table [DBO]. [salary] With nocheck add constraint [T1] Check ([salary]> = (1) and [salary] <= (1 )))
Go
Alter table [DBO]. [salary] Check constraint [T1]

Then a new stored procedure is created:

Use [test]
Go
/***** Object: storedprocedure [DBO]. [TT] script Date: 12/12/2007 14:25:44 ******/
Set ansi_nulls on
Go
Set quoted_identifier on
Go
-- ===================================================== ======
-- Author: <landscape>
-- Create Date: <2007-12-12>
-- Description: <Test>
-- ===================================================== ======
Create procedure [DBO]. [TT]
-- Add the parameters for the stored procedure here
As
Begin
-- Set nocount on added to prevent extra result sets from
-- Interfering with select statements.
Set nocount on;

-- Insert statements for procedure here
Begin tran
Save Tran Test
Insert salary values (5)
 
If (@ error <> 0)
Begin
Print '20140901'
Rollback Tran Test
End
Commit tran

End

Create another stored procedure:

Use [test]
Go
/***** Object: storedprocedure [DBO]. [TT] script Date: 12/12/2007 14:25:44 ******/
Set ansi_nulls on
Go
Set quoted_identifier on
Go
-- ===================================================== ======
-- Author: <landscape>
-- Create Date: <2007-12-12>
-- Description: <Test>
-- ===================================================== ======
Create procedure [DBO]. [TT1]
-- Add the parameters for the stored procedure here
As
Begin
-- Set nocount on added to prevent extra result sets from
-- Interfering with select statements.
Set nocount on;

-- Insert statements for procedure here
Begin tran
Save Tran Test
Insert salary values (5)
 
Commit tran

If (@ error <> 0)
Begin
Print '20140901'
Rollback Tran Test
End

End

Two test results:
1.

Message 547, level 16, status 0, process TT, 17th rows
The insert statement conflicts with the check constraint "T1. This conflict occurs in the Database "test", table "DBO. Salary", column 'salary '.
The statement has been terminated.
111

(One row is affected)

111 is output in the result.

2.

Message 547, level 16, status 0, procedure TT1, 17th rows
The insert statement conflicts with the check constraint "T1. This conflict occurs in the Database "test", table "DBO. Salary", column 'salary '.
The statement has been terminated.

(One row is affected)

No output in the result 111 indicates that the stored procedure is not executed.

Test environment: SQL Server 2005 Express

This is another example:

1. SQL Server Stored Procedure Transaction Processing
A common error handling mode is roughly as follows:
Create procdure prinsertproducts
(
@ Intproductid int,
@ Chvproductname varchar (30 ),
@ Intproductcount int
)
As
Declare @ interrorcode int
Select @ interrorcode = @ Error
Begin transaction
If @ interrorcode = 0
Begin
-Insert Products
Insert products (productid, productname, productcount)
S (@ intproductid, @ chvproductname, @ intproductcount)
Select @ interrorcode = @ error -- check every execution of a T-SQL statement and save the error code to a local variable.
End
If @ interrorcode = 0
Begin
-Update Products
Update products set productname = 'microcomputer' where productid = 5
Select @ interrorcode = @ Error
End
If @ interrorcode = 0
Commit transaction
Else
Rollback transaction

Return @ interrorcode -- it is best to return the error code to the called stored procedure or application

2. Use transactions in. net
Sqlconnection myconnection = new sqlconnection ("Data Source = localhost; initial catalog = northwind; Integrated Security = sspi ;");
Myconnection. open ();

Sqltransaction mytrans = myconnection *** gintransaction (); // use new to generate a transaction
Sqlcommand mycommand = new sqlcommand ();
Mycommand. Transaction = mytrans;

Try
{
Mycommand. commandtext = "Update address set location = '23 rain Street 'Where userid = '000000 '";
Mycommand. executenonquery ();
Mytrans. Commit ();
Console. writeline ("record is udated .");
}
Catch (exception E)
{
Mytrans. rollback ();
Console. writeline (E. tostring ());
Console. writeline ("sorry, record can not be updated .");
}
Finally
{
Myconnection. Close ();
}

Note: In sqlserver, each SQL statement is executed as a transaction, so it is stored in the stored procedure or in.. Net Code, there is no need to use transaction processing to execute a single SQL statement, the above is just to simplify the expression and use transaction processing for a single SQL statement

This is my testing for SQL beginners.