Join Martin Guidry for an in-depth discussion in this video Comparing triggers, functions, and procedures, part of SQL Server: Triggers, Stored Procedures, and Functions.
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.
- 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