Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
This course investigates several key database-programming concepts: triggers, stored procedures, functions, and .NET CLR (Common Language Runtime) assemblies. Author Martin Guidry shows how to combine these techniques and create a high-quality database using Microsoft SQL Server 2012. The course also covers real-world uses of the INSERT, UPDATE, and DELETE procedures, and how to build a basic web form to connect to your database.
Database developers can and should use transactions, to provide a logical grouping of actions. Transactions can happen inside of a stored procedure or outside of the stored procedure. I typically do most of my transactions inside of stored procedures. And we'll talk a few reasons about why. I have on the screen some code you can get from the exercise files. It's a basic stored procedure, that's going to either delete or deactivate an author. There's one UPDATE statement that runs on line 6, 7 and 8. That's going to set the particular author's active status to 0, meaning he is inactive.
And then lines 10, 11 and 12, set his phone number equal to Null. So this is a hypothetical where we want to stop tracking personal information about people that are no longer active. Lines 14 to 17, allow us to potentially roll- back the transaction if there's a problem. So this will help us avoid the situation, where we might end up with an author who we couldn't mark as inactive. We don't want to still remove his phone number. So fairly straightforward, I'm going to scroll down some more.
So we can see at the bottom where we're actually going to execute this stored procedure on John Doe. So we execute this and it does in fact mark John Doe as inactive. And set his phone number equal to null. This is certainly not the only way to do this. We could have created one store procedure that does the UPDATE statement on line 6, 7 and 8 and a different store procedure that does the UPDATE statement on 10, 11 and 12. And then run those to stored procedures as part of the same transaction.
From a developer point of view, I would rather do it the way it's on the screen here, where the structure of my code also reflects some business logic. Now these are two things that I've always want to be run together in the same transaction. And this type of mindset can often help us decide what to put into a single stored procedure versus when to create a new stored procedure. So anytime you are inside of a stored procedure, creating a second transaction, you should stop and think, hey, maybe that information, maybe that code, maybe that function should be moved to a different store procedure.
So if you have two transactions or three transactions inside of the same stored procedure, you need to think about maybe that should actually be broken out in to separate stored procedures. Conversely, if we have two stored procedures that we pretty much always run together and they always run as part of the same transaction, you need to stop and think about maybe we could combine those two stored procedures into one stored procedure, and in there put the business logic about the transactions. Meaning, we really have to think about the logic once but get it done in the store procedure right and never have to worry about it again.
There are currently no FAQs about SQL Server: Triggers, Stored Procedures, and Functions.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.