Shulou(Shulou.com)05/31 Report--

This article mainly introduces "SQLServer database how to use SQL statements to use transactions". In daily operation, I believe many people have doubts about how to use SQL statements to use transactions in SQLServer database. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "how to use SQL statements to use transactions in SQLServer database". Next, please follow the editor to study!

How to use SQL statements to use transactions in SQLServer Database

-- build a database

IFEXISTS (SELECTnameFROMsys.databasesWHEREname=N'TransTestDb') dropdatabase [TransTestDb] CREATEDATABASE [TransTestDb]

-- create a table

Use [TransTestDb] goIFEXISTS (SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID (N' [dbo]. [TransTestTable]') ANDtypein (Native U')) droptable [TransTestTable] CREATETABLE.TransTestTable (Idint, [Name] varchar (16))

-- initial value

Use[TransTestDb] goinsertinto[TransTestTable] select1,'a'unionselect2,'b'unionselect3,'c'

-- build a database

IFEXISTS (SELECTnameFROMsys.databasesWHEREname=N'TransTestDb') dropdatabase [TransTestDb] CREATEDATABASE [TransTestDb]

-- create a table

Use [TransTestDb] goIFEXISTS (SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID (N' [dbo]. [TransTestTable]') ANDtypein (Native U')) droptable [TransTestTable] CREATETABLE.TransTestTable (Idint, [Name] varchar (16))

-- initial value

Use[TransTestDb] goinsertinto[TransTestTable] select1,'a'unionselect2,'b'unionselect3,'c'

First of all, I introduce the use of SQL statements to use transactions. SqlServer2005/2008 provides three statements, begintran,committran and rollbacktran, to display the usage transactions. Begintran indicates the start of the transaction, committran indicates the transaction commits, and rollbacktran indicates the transaction rollback. The specific code is as follows:

How to use SQL statements to use transactions in SQLServer Database

Begintrybegintraninsertintodbo.TransTestTablevalues (66 Name); updatedbo.TransTestTableset [Name] = '88' where [ID] = 66 Matsumi Raiseror (' ErrorraisedinTRYblock.',16,1); committranendtrybegincatchrollbacktranendcatch

Begintry and begincatch in the code are used to catch exceptions and are only supported in sqlserver2005/2008, not on sqlserver2000. If an exception occurs while the code between begintry and endtry is running, the program will jump to begincatch and endcatch to perform the relevant rollbacktran rollback operation. There is a transaction between begintran and committran, and insert and update must succeed at the same time, or fail at the same time. The RAISERROR statement means to throw an exception, which is only supported in sqlserver2005/2008 and is not supported on sqlserver2000.

By executing the above code, we will find that both the insert and update are successful at the same time. After removing the comments from RAISERROR and then executing them, we will find that both inserts and updates have been rolled back. Because after RAISERROR throws an exception, it does not execute to committran, but directly executes the rollbacktran rollback statement in begincatch. Here you can see the atomicity principle and consistency principle of the transaction, while changing and inserting at the same time.

At this point, the study on "how the SQLServer database uses SQL statements to use transactions" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.