Tip:
Highlight text to annotate it
X
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.