SQL Server transactions, exceptions, and cursors (1/3)

In this tutorial, let's take a look at the SQL Server transactions, exceptions, and cursors. I hope this will help you. 1. Transaction features a transaction consists of several T-SQL commands, and all the commands last night a whole submitted to the database tutorial system, the execution of this set of commands either completed, or all canceled. Therefore

This tutorial is about SQL Server transactions, exceptions, and cursors. I hope it will help you. 1. Transaction features a transaction consists of several T-SQL commands, and all the commands last night a whole submitted to the database tutorial system, the execution of this set of commands either completed, or all canceled. Therefore

In this tutorialAboutSQL ServerTransactions,ExceptionAndCursorFor more information, see the tutorial.

1,TransactionsFeatures

TransactionsThere are several T-SQL commands, and all the commands were submitted to the database tutorial system one whole last night, the execution of this set of commands either completed or all canceled. Therefore,TransactionsIs an inseparable logical unit.

TransactionsIt has four attributes: Atomicity, Consistency, Isolation, and Durability.Transactions.

Atomicity:TransactionsAll the work in the instance is either completed or not completed.

Consistency:TransactionsAnd then the operation cannot violate the database and then constraints or rules,TransactionsThe internal data structure must be correct when finished.

Isolation:TransactionsThey are directly isolated from each other. If there are twoTransactionsPerform operations on the same database, such as reading table data. AnyTransactionsAll the content you see is eitherTransactionsThe status before completion, or otherTransactionsStatus after completion. OneTransactionsIt's impossible to meet anotherTransactions.

Durability:TransactionsAfter it is completed, the impact on the database system is persistent. Even if a system error occurs, after the system is restartedTransactionsThe result still exists.

2,Transactions.

A. DisplayTransactions

DisplayTransactionsIs clearly defined by the user using T-SQLTransactions(Begin transaction) and commit (commit transaction) or rollbackTransactions(Rollback transaction)

B. Automatic submissionTransactions

Automatic submissionTransactionsIs a type of Automatic Execution and automatic rollback.Transactions, Which is the default for the T-SQLTransactionsMethod. For example, when deleting a table record, if the record has a primary foreign key relationship, the deletion will be affected by the primary foreign key constraint, and the deletion will be canceled.

ConfigurableTransactionsImplicit mode: set implicit_transaction on;

C. ImplicitTransactions

ImplicitTransactionsWhenTransactionsAfter submission or rollback, SQL Server starts automaticallyTransactions. Therefore, implicitTransactionsYou do not need to use begin transaction to display the start point. You only need to submit the job directly.TransactionsOr roll backTransactionsT-SQL statement.

You must set the set implicit_transaction on statementTransactionsMode is enabled. The next statement starts a new transaction, and the next statement starts a new transaction.Transactions.

3,TransactionsProcessing

Common T-SQLTransactionsStatement:

A. begin transaction statement

StartTransactionsThe @ trancount global variable is used to recordTransactionsYou can use the @ error global variable to record the error information during execution. If there is no error, you can directly submit it.TransactionsAnd rollback can be performed if an error occurs.

B. commit transaction statement

RollbackTransactionsIndicates an implicit or displayedTransactionsThe modification made to the database takes effect. And subtract the value of @ trancount from 1;

C. rollback transaction statement

RollbackTransactionsAfter the rollback tran statement is executed, the data will be rolled back to the begin tran status

4,TransactionsExample

-- StartTransactions

 
 

  
  
Begin transaction tran_bank;
  
  
Declare @ tran_error int;
  
  
Set @ tran_error = 0;
  
  
Begin try
  
  
Update bank set totalMoney = totalMoney-10000 where userName = 'jack ';
  
  
Set @ tran_error = @ tran_error + @ error;
  
  
Update bank set totalMoney = totalMoney + 10000 where userName = 'jason ';
  
  
Set @ tran_error = @ tran_error + @ error;
  
  
End try
  
  
Begin catch
  
  
Print 'appears
  
  Exception, Error code: '+ convert (varchar, error_number () +', error message: '+ error_message ();
  
  
Set @ tran_error = @ tran_error + 1;
  
  
End catch
  
  
If (@ tran_error> 0)
  
  
Begin
  
  
-- Execution error, rollback
  
  Transactions
  
  
Rollback tran;
  
  
Print 'transfer failed, cancel transaction ';
  
  
End
  
  
Else
  
  
Begin
  
  
-- No
  
  Exception, Submit
  
  Transactions
  
  
Commit tran;
  
  
Print 'transfer successful ';
  
  
End
  
  
Go
 
 

ØException

In a program, sometimes when some Transact-SQL statements are completed, errors may occur,ExceptionInformation. If we want to handle this by ourselvesExceptionYou need to manually capture the information. We can use try catch.

TRY... The CATCH structure consists of a TRY block and a CATCH Block. If an error condition is detected in the Transact-SQL statement contained in the TRY block, the control is passed to the CATCH Block (this error can be handled in this block ).

The CATCH Block processesExceptionAfter an error occurs, the control is passed to the first Transact-SQL statement after the END CATCH statement. If the end catch statement is the last statement in a stored procedure or trigger, the control will return the code that calls the stored procedure or trigger. Do not execute the Transact-SQL statement after the wrong statement is generated in the TRY block.

If there are no errors in the TRY block, the control will be passed to the statement followed by the associated end catch statement. If the end catch statement is the last statement in a stored procedure or trigger, the control is passed to the statement that calls the stored procedure or trigger.

The TRY block starts with the begin try statement and ends with the end try statement. You can specify one or more Transact-SQL statements between the TRY in TRY and END TRY statements. The CATCH Block must be followed by the TRY block. The CATCH Block starts with the begin catch statement and ends with the end catch statement. In Transact-SQL, each TRY block is associated with only one CATCH block.

# Error functions

1 2 3
        
Articles you may be interested in
        
 
 

  
  
How to modify SQL Server 2005/2008 database path php how to connect to ms SQL Server database how to quickly obtain access, SQL Server Data SQL Server automatic acquisition of number field SQL Server fast deletion of duplicate records how to determine SQL Server the maximum value in the column fixes SQL Server MDF files. The SQL Server database crashes. SQL Server 2008 availability SQL Server 2008 R2 new features: master Data Management SQLServer quantitative performance analysis SQLServer execution plan cost
 
 
Previous Page:Time Comparison in SQL ServerDetails     Next page:Summary on how to compare the data of each group of two tables in SQL Server
AboutSQL ServerTransactions,ExceptionAndCursorDetailsComments (1/3)