Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
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.
Before we get into creating triggers, functions, and stored procedures let's talk about the similarities and differences. First, the similarities. All of these are database objects containing code that will be executed as a single unit. These things are similar to what other development platforms might call a method, a subroutine, or a module. Some other platforms also use the term procedure and function, similar to the way SQL Server uses those words. In SQL Server, triggers, functions, and stored procedures are typically written in a T-SQL, but they can also be implemented in any of the .NET languages like C# or VB.NET.
Now let's talk a little bit about the differences. Triggers and stored procedures are allowed to change data and commonly do change data, whereas a function is not allowed to change any data. The point of a function is always to return data. Sometimes it returns a single scalar value, sometimes it returns a table. Triggers never return data. A trigger has to accomplish all of its work by changing data and tables and there is no return value from a trigger. Stored procedures are somewhere in-between.
Typically, the point of a stored procedure is to change data in the underlying tables, but it is allowed to return a value if it wants to. It commonly returns a 0 or 1 to indicate success or failure. Probably, the biggest difference between the three is how they are called. The easiest to work with is a stored procedure. For a stored procedure, we just simply execute it directly from code using the phrase EXEC or the full word EXECUTE and then the name of the stored procedure.
This will cause a stored procedure to run at that time. We cannot use this technique with the other ones. So a function has to be part of a SELECT statement. So we see here we have a typical SELECT statement, calling first name and last name, but if I wrote a function that combined that into full name, I could just use that in line with the SELECT statement. I can use a function in the column list of a SELECT statement. I can also use it in the WHERE clause.
I could also use it in the ORDER BY clause of a single SELECT statement. These are all functions that return scalar values. It's also possible for a function to return an entire table and that will replace the table name here. We are not limited to just SELECT statements. we can also do the same thing with UPDATE, INSERT, and DELETE statements. So something like this where we're using the function to filter a DELETE statement.
Calling triggers is yet again a third technique. There is no way for me to issue a command that causes a trigger and just a trigger to run. A trigger always runs as a reaction to something. So I do something else on my machine, the machine performs that action, and then afterwards runs the trigger. Most commonly, triggers react to UPDATE, INSERT, or DELETE statements. So if I have a trigger set up on a table and I run an insert statement on that table, immediately after that insert the trigger will take over and execute its code.
There is no way to just type in and save run this trigger now.
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.