Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.
Users can group two or more Transact-SQL statements into a single transaction using the following statements:
Begin Transaction Rollback Transaction Commit TransactionTo understand transaction in depth see belowi am creating a table with three integer and one string type column
create Table Tbl_Sutdent_Record ([Subject] nvarchar(100),[WrittenMarks] int,[PracticalMarks] int,[TotalMarks] int )
In above table i insert some records with in a transaction as
Begin Transaction Trn_Insertation_Student_MarksBegin TryInsert into Tbl_Sutdent_Record values ('Hindi',50,55,105)Insert into Tbl_Sutdent_Record values ('English',55,35,90)Insert into Tbl_Sutdent_Record values ('CS',13,'a',27)Insert into Tbl_Sutdent_Record values ('ME',13,14,'b')Print'Worked Successfully'Commit Tran Trn_Insertation_Student_MarksEnd TryBegin CatchPrint'Errored Out'Rollback Tran Trn_Insertation_Student_MarksEnd Catch
on executing the above we get the out put
(1 row(s) affected)
Now when we select data from table as
Select * from Tbl_Sutdent_Record
then no record found because two records have wrong data (mismatch data type) so all command written in Transaction are rollback.