SQL Server Programming Part 14 - Transactions

[@mojgan2010]

You have a natural talent for teaching. I've followed all the videos on SSRS and SQL. The explanations are very clear and interesting. Thank you for providing this. It's very much appreciated.

[@navyahs4924]

Informative video covering many concepts of SQL programming. One more good thing is there is no time spent in typing the queries.

[@WiseOwlTutorials]

Thanks Navya, happy to hear you enjoyed it!

[@harishdevarapalli1537]

i am beginer to ssrs,but after seeing your tutorilas,i experienced a lot of stuff ,but i am very hapy to say that these videos furnishes ultimate stuff,excellent explination,conceptual,i salute,that can i say to uuuu

[@d3daiM]

you explain things so perfectly thank you.

[@marcin2x4]

Note sure but how SAVE TRAN is working in the stored procedure? (21:19) These videos are HUGE amount of knowledge!!!!

[@gamers542]

I find these SQL vids quite helpful as I am a newbie with SQL Server 2012. Couple things: 1.: Can you not set a breakpoint and debug auto rollback in a statement if you didn't want to put in error handling? 2.: Will there be a video on Triggers?

[@BijouBakson]

I know this is a very old video, but since this is one of the best resources I found on SQL I thought I'd still leave feedback regardless. At 12:00 the error message should have been something like: Cannot have more than 1 Iron Man 3; "Iron Man 3" already exist in the database. Why is this important? Because it keeps the focus on why you need to try and catch. I struggled for a moment to follow and I suspect others would too. Other than that, great content.

[@WiseOwlTutorials]

Thanks Bijou, appreciate the feedback!

[@Onella3DMusic]

omg this was amazinggg thank u so much!

[@WiseOwlTutorials]

You're welcome, thanks for watching!

[@8bitnitwit]

Thanks for uploading this. Very straightforward and easily understandable.

[@shawnhuang2277]

This very well explained how transaction worked. Thanks a lot!

[@codegate615]

crazy 7 years later still worth XD

[@krismaly]

I enjoyed this video and all videos in this series. But I couldn't get ERROR handling videos nor error handling explained in detail any video. I would appreciate if you could make some ERROR handling and how to get over them would be a good video. I recommend all your videos to watch and take training at your website/Center. Thanks

[@majoromer]

Please make audio volume for all wise owl tutorials higher

[@isabellearcadia7952]

Really wonderful ! So well explained ! Thank youuuuu !!!!!

[@michaeleichner7522]

Given your example if you did the following: Delete from tblFilm where filmName = ‘Iron man 3’ Begin tran Insert into tblFilm(filmName, filmReleaseDate) Values( ‘Iron man 3’, ‘2013-04-25’) Insert into tblFilm(filmName, filmReleaseDate) Values( ‘Iron man 3’, ‘2013-04-30’) Commit Tran I would have expected that a Select * from tblFilm where filmName = ‘Iron man 3’ Would return no rows because of the automatic rollback. But that’s not what happened. Instead the first insert works returning the row where filmReleaseDate = 2013-04-25. Why is the first record inserted? I thought that Transactions where all or nothing. Thanks

[@WiseOwlTutorials]

Hi Michael, if you wanted to see the behaviour you describe you would need to write the two INSERT statements within a TRY block. In the CATCH block you would need to include an explicit ROLLBACK statement. Then, if either of the two INSERT statements throw an exception, both would be rolled back. I hope that helps!

[@mankuzos1473]

This was quite useful and very well explained.

[@runenielsen8270]

Very good video! Helped me a lot in understanding transactions.

[@tuncaytnn]

It was great work Thank you for give more inf about save and rollback trans.

[@JI9131]

very good and easy to understand.

[@ghoulian]

outstanding!

[@pruthvirajgowda4566]

where did u mentioned the procedure name in 'exec'? can you explain me how it is working?

[@TheQciap]

Andrew, qq: Why in your assumption IF @IronMen is bigger than 1 not 0? IF @IronMen> 1 means that it can be already there once our movie in the list already. Even though you command "COMMIT TRAN". To me it will make it twice the same movie in our table or I am mistaken? Maybe too early for me in the morning ;) So to me it should be: IF @IronMen>0... BTW as always - great video!! :)

[@ionutzasaftei1989]

Awesome job. Good explanation of the concept. :)

[@MrHamuty]

Excellent tutorial!

[@sambarot3665]

You are awesome

[@laszloneduka8875]

Thank you.....

[@WiseOwlTutorials]

You're welcome!

[@S2100211]

I can not update the table tblFilm as I need to insert a value into the field FilmID as this field can not be Null. But I get the gist of what should be happening.

[@WiseOwlTutorials]

Sorry about that! I think that for this video I'd changed the design of the table but it's so long ago that I can't remember! Just for reference, you can allow insertion of values into an Identity column like so: SET IDENTITY_INSERT tblFilm ON Of course, if the identity column is a primary key you'll need to make sure that the value you're inserting is unique! I hope that helps!

[@librkvn3355]

Cant get the transaction to due to this error message! Cannot insert the value NULL into column 'FilmID', table 'Movies.dbo.tblFilm'; column does not allow nulls. INSERT fails. The statement has been terminated.

[@ipsitapani8641]

VERY NICE EXPLANATION ..THANK U SO MUCH. BUT I HAVE ONE DOUBT WHAT IS THE ROLE OF THIS LINE IN STOREPROCEDURE: SAVE TRAN ADDDIRECTOR. with thanks ipsita.

[@arnoldgyorgy545]

thank you very much!

[@jojogaotian]

thank u. it is very helpful!

[@ipsitapani8641]

Thanks a lot sir.thank u very much.

[@knutolansen7820]

linking because of content ;)

[@samargul6370]

very nice thanks alot

[@samankhajehzadeh4445]

Hello all, Can anyone help me please I ran the syntax below: INSERT INTO tblFilm (FilmName,FilmReleaseDate) VALUES ('Iron Man 3', '2014-04-25') And received the following message: Msg 515, Level 16, State 2, Line 1 Cannot insert the value NULL into column 'FilmID', table 'Movies.dbo.tblFilm'; column does not allow nulls. INSERT fails. The statement has been terminated. Thank you for your help. I am a beginner

[@samankhajehzadeh4445]

***** Thank you very much for your time Andrew. You are a legend.

[@samankhajehzadeh4445]

***** Hi Andrew, Please excuse me for writing such long messages. When I execute the same syntax as yours (21min45secs), I receive the following message 1. I seem to be missing/not understanding something. Thank you for your time in advance. (1 row(s) affected) Msg 515, Level 16, State 2, Procedure spGetDirector, Line 9 Cannot insert the value NULL into column 'DirectorID', table 'Movies.dbo.tblDirector'; column does not allow nulls. INSERT fails. The statement has been terminated. The 'spGetDirector' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. Msg 547, Level 16, State 0, Line 12 The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_tblFilm_tblDirector". The conflict occurred in database "Movies", table "dbo.tblDirector", column 'DirectorID'. The statement has been terminated.

[@samankhajehzadeh4445]

***** Dear Andrew, Many thanks for your comprehensive answer. Much appreciated. I made the changes you advised and now it works.

+WiseOwlTutorials can you tell me how alter column so the number is added automatically?

[@wasimzafri5224]

Your Videos Are Always Great .. Thanks !!!! :-)

[@ipsitapani8641]

SIR PLZ UPLOAD A VIDEO ON TRIGGER.

[@laszloneduka8875]

Thank you.....

[@WiseOwlTutorials]

You're welcome, thank you for watching!