SQL Server: Triggers, Stored Procedures, and Functions

SQL Server: Triggers, Stored Procedures, and Functions

with Martin Guidry

 


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.
Topics include:
  • Comparing triggers, functions, and stored procedures
  • Installing and configuring SQL Server
  • Creating a stored procedure
  • Returning data using data sets
  • Creating user-defined functions
  • Using "after," "instead," and nested triggers
  • Modifying existing stored procedures
  • Implementing logging on DELETE
  • Choosing between T-SQL and CLR
  • Executing a stored procedure
  • Passing parameters

show more

author
Martin Guidry
subject
Developer, Databases
software
SQL Server
level
Advanced
duration
1h 52m
released
Sep 24, 2012

Share this course

Learn more about lynda.com

Start learning today. Lynda explains how.

Ready to join? subscribe

Keep up with news, tips, and latest courses.

submit Course details submit clicked more info

Please wait...

Search the closed captioning text for this course by entering the keyword you’d like to search, or browse the closed captioning text by selecting the chapter name below and choosing the video title you’d like to review.



Introduction
Welcome
Collapse this transcript
What you should know
Collapse this transcript
Using the exercise files
Collapse this transcript
1. Getting Started
Comparing triggers, functions, and procedures
Collapse this transcript
Why use a stored procedure?
Collapse this transcript
Why use functions?
Collapse this transcript
Why use triggers?
Collapse this transcript
2. Installing SQL Server
Configuring your environment
Collapse this transcript
Downloading and installing a sample database
Collapse this transcript
3. Stored Procedures
Creating a stored procedure
Collapse this transcript
Modifying a stored procedure
Collapse this transcript
Returning data using data sets
Collapse this transcript
Returning data using cursors
Collapse this transcript
Using input and output parameters
Collapse this transcript
Using security and permissions
Collapse this transcript
Using transactions
Collapse this transcript
4. User-Defined Functions
Creating a user-defined function
Collapse this transcript
Exploring single-value functions
Collapse this transcript
Exploring table value functions
Collapse this transcript
5. Triggers
Using "after" triggers
Collapse this transcript
Using "instead of" triggers
Collapse this transcript
Using nested triggers
Collapse this transcript
Using database-level triggers
Collapse this transcript
6. Putting It All Together
Exploring a real-world INSERT procedure
Collapse this transcript
Exploring a real-world UPDATE procedure
Collapse this transcript
Implementing logging on DELETE
Collapse this transcript
7. Using CLR Assemblies
Understanding the Common Language Runtime (CLR) and the .NET framework
Collapse this transcript
Using CLR with SQL Server 2012
Collapse this transcript
Writing stored procedures with C# .NET
Collapse this transcript
Writing functions with .NET
Collapse this transcript
Choosing between T-SQL vs. CLR
Collapse this transcript
8. Exploring an ASP.NET Web Application
Creating a basic web form and connecting to a database
00:00Now let's look at assembling some of the pieces we're going to use to create our
00:04web application that will communicate with our SQL Server stored procedures.
00:08Here in Visual Web Developer, I am going to create a new website.
00:13I'd like to use the template--a C# template for an ASP.NET Empty Web Site.
00:18I'd like to drop that in our Exercise folder and we'll call it the AuthorsSite.
00:25So this gives me an empty solution with no data in there.
00:32I'd like to add a new basic web form, so I'll right-click and say Add New Item,
00:37I'd like to add a web form and I'd like to call that web form, AuthorsPage.
00:42The machine will write some code for me, but that--I'm not going to use that.
00:49I've already written some code that should be in the exercise files.
00:53We'll look at the code real quick;
00:55we have some styles that we're setting up.
00:57Two styles, one for the headings, and one for the body, and then we have a
01:01table, that's creating a space for a list of all active authors, and another
01:06space for us to create a form to insert new authors.
01:11If we flip over to Design view, we can see this, our two headings and two empty
01:16spaces available and ready for us to put exciting stuff in there.
01:20Now I'd like to bring this home with something a little more practical.
01:24Obviously, users won't go directly to the database to update and insert records.
01:29More likely, they would go to an application or a website to manipulate the
01:33records in our database.
01:35So I am going to go ahead and create a small web application that connects to our database.
01:40It'll use some stored procedures and functions, and we'll see that if we take
01:44some time and effort to set up our stored procedures and functions correctly,
01:48then the level of effort and the amount of code we have to write on the
01:52website is very easy.
01:54The next thing we'd like to do is create a connection between our web
01:57application and our database.
01:59We'll do this off of a Window called Database Explorer.
02:03Inside of Database Explore, the top option is Data Connections.
02:06We can right-click that and say Add Connection.
02:09Up there you'll need to give it the name of your SQL Server or the IP address. There is mine.
02:15We have the option to Use Windows Authentication of SQL Authentication.
02:19I'm going to stick with Windows and we will need to choose which database
02:23we're interested in;
02:24my database that we were working in previous examples, hit Test Connection, it says succeeded.
02:30That's good.
02:32And now we see a new connection named thusly, basically it's the name of our
02:36server.name of the database.
02:39To open that up in Explorer, we see the tables we were working with previously.
02:44And we also see the stored procedures we worked with previously, and the
02:48functions we worked with previously.
02:50So we are very close to being able to run the stored procedures off of the
02:55web page.
Collapse this transcript
Executing a stored procedure
Collapse this transcript
Passing parameters
Collapse this transcript
Getting return values
Collapse this transcript
Conclusion
Next steps
Collapse this transcript


Suggested courses to watch next:

SQL Essential Training (2h 22m)
Bill Weinman

SQL Server 2008 Essential Training (6h 54m)
Simon Allardice


Are you sure you want to delete this bookmark?

cancel

Bookmark this Tutorial

Name

Description

{0} characters left

Tags

Separate tags with a space. Use quotes around multi-word tags. Suggested Tags:
loading
cancel

bookmark this course

{0} characters left Separate tags with a space. Use quotes around multi-word tags. Suggested Tags:
loading
cancel

Error:

go to playlists »

Create new playlist

name:
description:
save cancel

You must be a lynda.com member to watch this video.

Every course in the lynda.com library contains free videos that let you assess the quality of our tutorials before you subscribe—just click on the blue links to watch them. Become a member to access all 98,648 instructional videos.

start free trial learn more

If you are already an active lynda.com member, please log in to access the lynda.com library.

Get access to all lynda.com videos

You are currently signed into your admin account, which doesn't let you view lynda.com videos. For full access to the lynda.com library, log in through iplogin.lynda.com, or sign in through your organization's portal. You may also request a user account by calling 1 1 (888) 335-9632 or emailing us at cs@lynda.com.

Get access to all lynda.com videos

You are currently signed into your admin account, which doesn't let you view lynda.com videos. For full access to the lynda.com library, log in through iplogin.lynda.com, or sign in through your organization's portal. You may also request a user account by calling 1 1 (888) 335-9632 or emailing us at cs@lynda.com.

Access to lynda.com videos

Your organization has a limited access membership to the lynda.com library that allows access to only a specific, limited selection of courses.

You don't have access to this video.

You're logged in as an account administrator, but your membership is not active.

Contact a Training Solutions Advisor at 1 (888) 335-9632.

How to access this video.

If this course is one of your five classes, then your class currently isn't in session.

If you want to watch this video and it is not part of your class, upgrade your membership for unlimited access to the full library of 1,899 courses anytime, anywhere.

learn more upgrade

You can always watch the free content included in every course.

Questions? Call Customer Service at 1 1 (888) 335-9632 or email cs@lynda.com.

You don't have access to this video.

You're logged in as an account administrator, but your membership is no longer active. You can still access reports and account information.

To reactivate your account, contact a Training Solutions Advisor at 1 1 (888) 335-9632.

Need help accessing this video?

You can't access this video from your master administrator account.

Call Customer Service at 1 1 (888) 335-9632 or email cs@lynda.com for help accessing this video.


site feedback

Thanks for signing up.

We’ll send you a confirmation email shortly.

By signing up, you’ll receive about four emails per month, including

We’ll only use your email address to send you these mailings.

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

By signing up, you’ll receive about four emails per month, including

We’ll only use your email address to send you these mailings.

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked