SQL server transaction example

I used to perform database operations in the past, but I seldom used transactions. When I was working on a project recently, I found that occasionally less data was written to the database, and no error was reported. After reading this for a long time, I don't know why.
Divide an insert statement into two writes, and the data will not be lost. It is suspected that the written field is too large. Later, I thought it was not safe, so I used transactions directly.

The following is an example of a simple transaction I wrote. It is used for querying dining allowances of units. It is mainly used to judge the meal type based on the dining records. Adding subsidies to different people is only an operation.
You can also do it using workbooks. You can watch the workbooks for a long time, but you can't help it. You have made a simple query and I will talk about it.

Create procedure [dbo]. [AddBZmoney] AS --- determine the meal type and subsidy amount
Begin
Begin
Tran

Insert into SumBZ (card number, date, meal, meal amount) -- Summary of daily meal details

SELECT Detail. card number, Detail. Meal date, dbo. GetBZType (Detail. meal time) AS meal,
-- Query meal amount
SUM (Detail. Consumption amount) AS consumption amount
FROM Detail INNER JOIN

BZType ON dbo. GetBZType (Detail. meal time) = BZType. Meal
WHERE (Detail. Status =
0)
Group by Detail. Dining date, dbo. GetBZType (Detail. Dining time), Detail. card number

Update Detail set status = 1 where status = 0 -- update subsidy status

Create table TempBZ -- create an intermediate table to determine the subsidy amount
(
ID int,
BZmoney money
)

Insert into TempBZ
SELECT SumBZ. ID,
CASE
WHEN SumBZ. Meal amount
> = BZType. subsidy standard and People. Overtime Allowance = 1 and SumBZ. Meal = 'dinner 'THEN
BZType. subsidy standard-Overtime Allowance for dinner, more than 4 yuan for 4 yuan
When SumBZ. Meal amount> = BZType. subsidy standard and
People. Overtime Allowance = 0 and SumBZ. Meals = 'bie' then 0 -- if there is no overtime allowance for dinner, make up 0 yuan
When SumBZ. Meal amount
> = BZType. subsidy standard and SumBZ. meal type = 'lunch 'then BZType. subsidy standard-if all lunch items exceed 3 RMB, Supplement 3 RMB
When
SumBZ. Meal = 'Breakfast 'then BZType. subsidy standard-no breakfast supplements
WHEN SumBZ. Meal amount <
BZType. subsidy standard THEN 0 -- do not add if the subsidy quota is not exceeded
End
FROM SumBZ INNER JOIN

BZType ON SumBZ. Meal = BZType. Meal inner join People On People. Card number = SumBZ. card number

Declare @ I int
Set @ I = (select min (ID) from TempBZ)
While
@ I <= (select max (ID) from TempBZ)
Begin
Update SumBZ set
Subsidy amount = (select BZmoney from TempBZ where ID = @ I
)
Where ID = @ I

Set @ I = @ I + 1
End
Drop
Table TempBZ
If (@ error! = 0)
Begin
Rollback tran
Return (1)
End
Commit
Tran
End
GO