sql transaction set is used when criteria like below,
I have two tables
PurchaseMain (BillNo,BillDate,BillTotalAmt)
PurchaseDetails (BillNo(FK),ProductId,ProductQty,ProductRate,ProductPrice)
you can understand above structure,
it is obvious while inserting data if any product from list is not saved then you need to rollback whole Bill. (Rollback discard saved data which is logically incomplete - here without products no mean to keep entry in purchase main table.)
in this case transaction se can be used
e.g.
begin transaction tran_purchase
--entry in purchase main
--for loop to add products in purchase details
--using purchase main new bill no entry in Purchasedetail
--if error then RollBack transaction tran_purchase and return ...means error occur then rollback changes and terminate execution no need to execute next line.
--end of for loop
commit transaction tran_purchase ... means save transaction no error found
some times it's used for consistency with locking concepts
e.g.
Vehicle number allocation
vehicle no. are alphaNumeric it's not int so that we have to manually produce it
at a time from different city branches in a state multiple command will given to database for generate vehicle no
so, chances of duplicate nos generation for single vehicle
this criteria suppose vehicle no is GJ-1-ZZ-9789
begin transaction tranVehiclenoGeneration with Lock level
--trim 4 digit from rigthside
--increase no by 1
--check in database GJ-1-ZZ-9790 new no exist if no then go ahead ... same way for middle 'zz' need to generate next series so, it's dependancy at that time no oher record should be added in table else it can make calculaions wrong if procedure is meanwhile
--so this case we need to keep logic in transaction set with suitable lock concept
--at end commit/rollback transaction will realease lock
Happy Coding!
:)
Submit an article or tip