Set implicit_transactions {on | off}

If set to on, set implicit_transactions sets the connection to implicit transaction mode. If it is set to off, the connection is restored to the automatic commit transaction mode.

Set implicit_transactions on -- the user must submit or roll back explicitly each time. Otherwise, when the user disconnects,
-- The transaction and all its data changes will be rolled back.
Set implicit_transactions off -- automatic submission mode. In the automatic submission mode, if each statement is successful
-- Submit the job.

SQL transactions
 
I. Transaction Concept
A transaction is a mechanism and a sequence of operations. It contains a set of database operation commands. These commands are either executed in full or not executed in full. Therefore, a transaction is an inseparable unit of work logic. Executing concurrent operations on the database system is used as the smallest control unit. This is especially suitable for data communication systems operated by multiple users at the same time. For example, ticket booking, banking, insurance companies, and securities trading systems.
 
Ii. Transaction attributes
Four attributes of transactions:
Atomicity: a transaction is a complete operation.
2. Consistency: when the transaction is completed, the data must be consistent.
3 isolation: all concurrent transactions that modify data are isolated from each other.
4 durability: after a transaction is completed, its impact on the system is permanent.
 
3. Create a transaction
Statement for managing transactions in the T-SQL:
1. Start transaction: Begin transaction
2. Submit the transaction: commit transaction
3. rollback transaction: rollback transaction
 
Transaction category:
1. Explicit transaction: Use begin transaction to specify the start of the transaction.
2. Implicit Transaction: Open the implicit transaction: Set implicit_transactions on. When you operate in implicit transaction mode, SQL servler automatically starts a new transaction after committing or rolling back the transaction. The start of the transaction cannot be described. You only need to commit or roll back the transaction.
3 Automatic commit transaction: the default mode of SQL Server, which treats each individual T-SQL statement as a transaction. If the task is successfully executed, the task is automatically submitted. Otherwise, the task is rolled back.
 
For example, Michael transferred 800 yuan to the Li Si account.
Use studb
Go
-- Create account table bank --
If exists (select * From sysobjects where name = 'bank ')
Drop table bank
Create Table bank
(
Customername char (10), -- CUSTOMER NAME
Currentmoney money -- Current Balance
)
Go
/** // * -- Add constraints. The account cannot be less than RMB --*/
Alter table bank add
Constraint ck_currentmoney check (currentmoney> = 1)
/** // * -- Insert test data --*/
Insert into Bank (customername, currentmoney)
Select 'zhang san', 1000 Union
Select 'Li si', 1

Select * from bank
Go

/** // * -- Use a transaction --*/
Use studb
Go
-- Restore original data
-- Update Bank set currentmoney = currentMoney-1000 where customername = 'lil'
Set nocount on -- do not display affected rows
Print 'view the balance before the transfer transaction'
Select * from bank
Go

/** // * -- Start the transaction --*/
Begin transaction
Declare @ errorsum int -- defines variables used to accumulate errors during transaction execution
/** // * -- Transfer --*/
Update Bank set currentmoney = currentMoney-800 where customername = 'zhang san'
Set @ errorsum = @ errorsum + @ error -- indicates whether an error exists.
Update Bank set currentmoney = currentmoney + 800 where customername = 'Li si'
Set @ errorsum = @ errorsum + @ error -- indicates whether an error exists.

Print 'view the balance in the transfer transaction'
Select * from bank

/** // * -- Determines whether the transaction is committed or rolled back based on whether an error exists --*/
If @ errorsum> 0
Begin
Print 'transaction failed, rollback transaction .'
Rollback transaction
End
Else
Begin
Print 'transaction successful, submit transaction, write to hard disk, save permanently! '
Commit transaction
End
Go

Print 'view the balance after transfer'
Select * from bank
Go