Loading ...

Transactions in SQL Server

 /5
0 (0votes)

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 Transaction
To  understand transaction in depth see below
i 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_Marks
Begin Try
Insert 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_Marks
End Try
Begin Catch
Print
'Errored Out'
Rollback Tran Trn_Insertation_Student_Marks
End Catch

on executing the above we  get the   out put


(1 row(s) affected)

(1 row(s) affected)

Errored Out

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.

Comments (no comments yet)

Top Posts