caska回滚SQL：通过ROLLBACKSQL查询回滚事务
The rollback SQL statement is ud to manually rollback transactions in MS SQL Server.
回滚SQL语句⽤于在MS SQL Server中⼿动回滚事务。
Transactions in SQL Server are ud to execute a t of SQL statements in a group. With transactions, either all the statements in a group execute or none of the statements execute.
SQL Server中的事务⽤于在⼀个组中执⾏⼀组SQL语句。 使⽤事务时，将执⾏组中的所有语句，或者不执⾏任何语句。
In the ca where one of the queries in a group of queries executed by a transaction fails, all the previously executed queries are rollbacked. Transactions in the SQL rver are rollbacked automatically. However, with the rollback SQL statement, you can manually rollback a transaction bad on certain conditions.
在由事务执⾏的⼀组查询中的⼀个查询失败的情况下，将回滚所有先前执⾏的查询。 SQL Server中的事务会⾃动回滚。 但是，使⽤回滚SQL语句，您可以根据特定条件⼿动回滚事务。
In this article, you will e what a transaction is and how it can be rollbacked both manually and automatically.
在本⽂中，您将看到什么是事务以及如何⼿动和⾃动回滚事务。
First, let’s create a dummy datat for you to practice on unless you are 100% confident that your .
⾸先，让我们创建⼀个虚拟数据集供您练习，除⾮您100％确信 。
创建⼀个虚拟数据库 (Create a dummy databa)
The following script creates a dummy databa named BookStore with one table, i.e., Books. The Books table has four columns: id, name, category, and price:
以下脚本使⽤⼀个表（即Books）创建⼀个名为BookStore的虚拟数据库。 Books表包含四列： id ， name ， category和price ：
CREATE Databa BookStore;
GO
USE BookStore;
CREATE TABLE Books
(
id INT,
name VARCHAR(50) NOT NULL,
category VARCHAR(50) NOT NULL,
price INT NOT NULL
)
Let’s now add some dummy records in the Books table:
现在让我们在Books表中添加⼀些虚拟记录：
USE BookStore
INSERT INTO Books
VALUES
(1, 'Book1', 'Cat1', 1800),
(2, 'Book2', 'Cat2', 1500),
(3, 'Book3', 'Cat3', 2000),
(4, 'Book4', 'Cat4', 1300),
(5, 'Book5', 'Cat5', 1500),
歌舞青春3下载(6, 'Book6', 'Cat6', 5000),
男生圆脸发型
(7, 'Book7', 'Cat7', 8000),
(8, 'Book8', 'Cat8', 5000),
(9, 'Book9', 'Cat9', 5400),
(10, 'Book10', 'Cat10', 3200)
The above script adds 10 dummy records to the Books table.
上⾯的脚本将10条虚拟记录添加到Books表中。
在不使⽤事务的情况下执⾏多个查询 (Executing multiple queries without using transactions)
In this ction, we will e the problems that occur if we execute multiple queries in a group without transactions. In the latter ction, we will e how transactions can be ud to automatically and manually rollback SQL queries and deal with the issues.
在本节中，我们将看到如果在没有事务的组中执⾏多个查询，则会出现问题。 在后⾯的部分中，我们将看到如何使⽤事务来⾃动和⼿动回滚SQL查询并处理这些问题。
Look at the following script:
看下⾯的脚本：
INSERT INTO Books
VALUES (15, 'Book15', 'Cat5', 2000)
UPDATE Books
SET price = '25 Hundred' WHERE id = 15
DELETE from Books
WHERE id = 15
In the script above, we execute three queries. The first query inrts a new record in the Books table where the id of the record is 15. The cond query updates the price of the book with id 15. Finally, the third query deletes the record with id 15. If you execute the above query, you should e the following error:
在上⾯的脚本中，我们执⾏三个查询。 第⼀个查询在Books表中插⼊⼀条新记录，该记录的ID为15。第⼆个查询更新ID为15的书的价格。最后，第三个查询删除ID为15的记录。查询，您应该看到以下错误：学费英文
The error is pretty lf-explanatory. It says that we cannot assign the string value ‘25 Hundred’ to the ‘id’ column, which is of integer type. Hence, the cond query fails to execute. However, the problem with the above script is that while the cond query fails, the first query still executes. You can verify this by lecting all the records from the Books table, as shown below:
该错误是不⾔⾃明的。 它说我们不能将字符串值“ 25 Hundred”分配给整数类型的“ id”列。 因此，第⼆个查询⽆法执⾏。 但是，上述脚本的问题在于，尽管第⼆个查询失败，但第⼀个查询仍然执⾏。 您可以通过从“书籍”表中选择所有记录来验证这⼀点，如下所⽰：
What if you really want is that if the cond query fails, the first query should be rollbacked as well so
that you go back to how you were before you executed the queries?
如果您真正想要的是，如果第⼆个查询失败，那么也应该回滚第⼀个查询，以便回到执⾏查询之前的状态？
To achieve this, you need to u transactions.
为此，您需要使⽤事务。
⾃动回滚SQL事务 (Automatically rollback SQL transactions)认为的英语
As I said earlier, if one of the queries in a group of queries executed inside a transaction fails, all the previously executed SQL statements are rollbacked. Let’s e how transactions can be ud to rollback SQL queries:
如前所述，如果在事务内执⾏的⼀组查询中的⼀个查询失败，则将回滚所有先前执⾏SQL语句。 让我们看看如何使⽤事务回滚SQL查询：BEGIN TRANSACTION
INSERT INTO Books
VALUES (20, 'Book15', 'Cat5', 2000)
UPDATE Books
SET price = '25 Hundred' WHERE id = 20
DELETE from Books
WHERE id = 20
COMMIT TRANSACTION
To start a transaction, the BEGIN TRANSACTION statement is ud, followed by the t of queries that you want to execute inside the transaction. To mark the end of a transaction, the COMMIT TRANSACTION statement can be ud.
要开始事务，将使⽤BEGIN TRANSACTION语句，然后使⽤要在事务内部执⾏的查询集。 为了标记事务的结束，可以使⽤COMMIT TRANSACTION语句。
In the script above, we execute the same three SQL queries that we did in the last ction. However, this time the queries have been executed inside a transaction. Again, the first query will execute successfully and an error will occur while executing the cond query. Since the queries are being e
xecuted inside a transaction, the failure of the cond query will cau all the previously executed queries to rollback. Now, if you lect all the records from the Books table, you will not e the new record with id 20, inrted by the first query inside the transaction.
在上⾯的脚本中，我们执⾏与上⼀部分相同的三个SQL查询。 但是，这⼀次查询已在事务内执⾏。 同样，第⼀个查询将成功执⾏，并且在执⾏第⼆个查询时将发⽣错误。 由于查询是在事务内部执⾏的，因此第⼆个查询的失败将导致所有先前执⾏的查询回滚。 现在，如果您从Books表中选择所有记录，您将看不到ID为20的新记录，该记录是由事务内部的第⼀个查询插⼊的。
⼿动回滚SQL事务 (Manually rollback SQL transactions)
In the previous ction, you saw how transactions automatically rollback themlves if one of the queries cannot be executed successfully. However, you may want to rollback a query bad on certain conditions as well. For example, you may want to rollback a transaction that inrts a record in the books table if a book with the same name already exists.
物流师培训中心在上⼀节中，您了解了如果其中⼀个查询⽆法成功执⾏，事务如何⾃动回滚⾃⾝。 但是，您可能还希望根据某些条件回滚查询。 例如，您可能想回滚⼀个事务，如果已经存在同名的书，则该事务会在books表中插⼊⼀条记录。
In that ca, you can u the rollback SQL statement.
在这种情况下，您可以使⽤回滚SQL语句。
Look at the following example:
看下⾯的例⼦：
DECLARE @BookCount int
BEGIN TRANSACTION AddBook
INSERT INTO Books
VALUES (20, 'Book15', 'Cat5', 2000)
SELECT @BookCount = COUNT(*) FROM Books WHERE name = 'Book15'
IF @BookCount > 1
cairBEGIN
ROLLBACK TRANSACTION AddBook
drinks是什么意思
buptPRINT 'A book with the same name already exists'
END
ELSE
BEGIN
COMMIT TRANSACTION AddStudentwww 900game net
PRINT 'New book added successfully'
END
In the script above, we declare a variable @BookCount. Next, we create a transaction named AddBook. To create a named transaction, you simply have to pass any string name for the transaction after the BEGIN TRANSACTION statement.
在上⾯的脚本中，我们声明⼀个变量@BookCount。 接下来，我们创建⼀个名为AddBook的事务。
要创建命名事务，您只需在BEGIN TRANSACTION语句之后传递该事务的任何字符串名称。
Inside the transaction, a book with id 20 and name Book15 is inrted in the Books table. After that, the COUNT function is ud to count the Books with the name Book15.
在事务内部，将ID为20且名称为Book15的书插⼊Books表中。 之后，使⽤COUNT函数对名称为Book15的书进⾏计数。
If the count is greater than 1, that means a book already exists with the name Book15. In this ca, the rollback SQL statement is ud to rollback the AddBook transaction manually; otherwi, the transaction will be committed and an appropriate message is displayed to the reader.
如果计数⼤于1，则表⽰⼀本名为Book15的书已经存在。 在这种情况下，使⽤回滚SQL语句⼿动回滚AddBook事务。 否则，事务将被提交，并向阅读器显⽰⼀条适当的消息。
You can e that the syntax of the rollback SQL statement is simple. You just have to write the statement ROLLBACK TRANSACTION, followed by the name of the transaction that you want to rollback.
您可以看到回滚SQL语句的语法很简单。 您只需要编写语句ROLLBACK TRANSACTION，后跟要回
滚的事务的名称。
Now, try to run the AddBook transaction to inrt the record where the name is Book15 (make sure that no book with this name already exists in the Books table).
现在，尝试运⾏AddBook事务以插⼊名称为Book15的记录（确保“书籍”表中不存在具有该名称的书籍）。
You will e that the transaction will execute successfully, and the following message will be displayed to the reader:
您将看到事务将成功执⾏，并且以下消息将显⽰给阅读器：
Now, again try to run the AddBook transaction. You will e that this time the transaction will fail since a book with the name Book15 already exists in the databa. Therefore the transaction will be rolled back with the following message displayed to the ur:
现在，再次尝试运⾏AddBook事务。 您将看到这次交易将失败，因为数据库中已经存在名称为Book15的书。 因此，该事务将回滚，并向⽤户显⽰以下消息：
结论 (Conclusion)
The article explains how to rollback SQL queries using transactions. Queries can be automatically or manually rolled back via transactions. Automatic rollback happens when a query fails to execute for any reason. Manual rollback occurs depending on ur-defined conditions. The rollback SQL statement is ud to manually rollback SQL queries in SQL Server.
本⽂介绍了如何使⽤事务回滚SQL查询。 查询可以通过事务⾃动或⼿动回滚。 当查询由于任何原因⽽⽆法执⾏时，就会发⽣⾃动回滚。 ⼿动回滚取决于⽤户定义的条件。 回滚SQL语句⽤于在SQL Server中⼿动回滚SQL查询。
