Mohamed Ahmed Mohamed Ahmed - 24 days ago 7
SQL Question

Transaction doesn't rollback even there was an error?

I created this transaction (my first transaction), there is no row with

Id = 111
in
Teacher
table, however it works on the
Subject
table and inserted a new row. Shouldn't it rollback all the changes?

BEGIN TRANSACTION
INSERT INTO Subject (Name, SupervisorId) VALUES('Statistics', 4)
UPDATE Teacher SET Name ='Hady' WHERE Id=111
COMMIT TRANSACTION


I then add some more logic to it, but still not working:

Begin Try
BEGIN TRANSACTION
INSERT INTO Subject (Name, SupervisorId) VALUES('Statistics', 4)
UPDATE TeacherO SET Name ='Hady' WHERE Id=111
COMMIT TRANSACTION
End Try
Begin Catch
ROLLBACK TRANSACTION
End Catch

Answer

If I understand this correctly, you assume, that the attempt to update a row in teacher table when there is no row with Id = 111 in Teacher table was an error...

An error occurs, when you do something forbidden or impossible. If you try to add a number to a string without casts. Or if you try to get hands on a not exisiting object. Maybe you want to convert 30.02.2016 to date. All will be an error.

But if you tell the database to update all rows where the id=111, exactly this happens: All rows with id=111 will be updated. The count of affected rows will be zero in your case. But this is not an error...