Why use transactions?
When updating multiple tables, an execution fails. Transaction rollback is required to maintain data integrity.
Display Set transactions
Copy codeThe Code is 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
Implicitly set transactions
Copy codeThe Code is as follows:
Set implicit_transactions on; -- start implicit transactions
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; -- roll back the transaction
End catch
Set implicit_transactions off; -- disable implicit transactions
Go
Show that the following statements of the transaction are not available, and the implicit transaction can
Copy codeThe Code is as follows:
Alter database;
Backup;
Create database;
Drop database;
Reconfigure;
Restore;
Update statistics;
Show that transactions can be nested
Copy codeThe Code is 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 the Save point
Commit transaction innerTrans
End catch
End
Go
Begin transaction outrans
Exec qiantaoProc 'asdasd ';
Rollback transaction outrans
Transaction nesting. When an outer transaction is rolled back, an exception occurs if the nested transaction has been rolled back. In this case, you need to use the transaction storage point. The above code.