Why Use Transactions
When more than one table is updated, an execution of the article fails. In order to maintain the integrity of the data, transaction rollback is required.
Show settings transaction
Copy Code code as follows:
Begin try
BEGIN TRANSACTION
Insert into Shiwu (ASD) VALUES (' AASDASDA ');
Commit TRANSACTION
End Try
Begin Catch
Select Error_number () as ErrorNumber
ROLLBACK TRANSACTION
End Catch
Implicit setting of transactions
Copy Code code as follows:
Set implicit_transactions on; --Start an implicit transaction
Go
Begin try
Insert into Shiwu (ASD) VALUES (' AASDASDA ');
Insert into Shiwu (ASD) VALUES (' AASDASDA ');
Commit TRANSACTION;
End Try
Begin Catch
Select Error_number () as ErrorNumber
ROLLBACK TRANSACTION; --Rolling back transactions
End Catch
Set Implicit_transactions off; --Turn off implicit transactions
Go
Show transactions The following statement cannot be used, and an implicit transaction can
Copy Code code as follows:
ALTER DATABASE;
Backup
Create database;
drop database;
Reconfigure;
Restore
Update STATISTICS;
Show transactions can be nested using
Copy Code code as follows:
--Create a stored procedure
CREATE PROCEDURE Qiantaoproc
@asd NCHAR (10)
As
Begin
Begin try
BEGIN TRANSACTION Innertrans
Save Transaction SavePoint--Create a transaction save point
Insert into Shiwu (ASD) values (@asd);
Commit TRANSACTION Innertrans
End Try
Begin Catch
ROLLBACK TRANSACTION SavePoint--roll back to save point
Commit TRANSACTION Innertrans
End Catch
End
Go
BEGIN TRANSACTION Outrans
exec qiantaoproc ' ASDASD ';
ROLLBACK TRANSACTION Outrans
Transaction nesting, when the outer transaction is rolled back, there is an exception if the transaction within the nesting has been rolled back. You need to use the transaction savepoint at this point. As on the code.
