1. Trigger alternatives
So far we've talked about the types of triggers and the scenarios that best fit their use.
But as with any feature, triggers are not always the best solution in SQL Server.
In this lesson we will compare triggers with other objects and take a look at the appropriate situations for using them.
2. Triggers vs. stored procedures
The first comparison of triggers is with regular stored procedures.
The trigger is a stored procedure as well, but a special kind. What makes it so special?
A trigger is fired when a defined database or server event occurs.
Regular stored procedures run when called explicitly. A trigger runs only when the event that fires it is starting.
Triggers don't allow input parameters or transaction statements (like BEGIN TRANSACTION or COMMIT). They also cannot return output values.
On the other hand, regular stored procedures allow input parameters, transaction statements, and the possibility to return output.
3. Triggers vs. stored procedures
These considerations impact the real-life use cases for triggers and regular stored procedures.
We use triggers for audit and to enforce integrity in most cases, and we use regular stored procedures for more general tasks.
4. Triggers vs. computed columns
The second comparison of triggers is with computed columns.
Computed columns are a good way to automate calculation of the values contained by some columns.
Computed column values are determined based on values from other columns, but only from the same table.
This limitation can be overcome by using triggers.
A trigger can use columns from other tables as well to calculate values.
While this calculation will be done with INSERT or UPDATE statements when using a trigger, for a calculated column it will be part of the table definition.
5. Example of a computed column
In this example, the column "TotalAmount" is a computed column.
Its value comes from the multiplication of the "Quantity" and "Price" columns from the same table.
But if those two columns were not part of the same table, we wouldn't be able to do the calculation using a computed column.
6. Using a trigger as a computed column
In that case, we could replace the computed column with this trigger definition.
Here, the Price column is not part of the "SalesWithoutPrice" table. The trigger determines the "TotalAmount" by using the "Price" column from another table ("Products" in this example).
7. Let's compare them in practice!
Now that you've seen how triggers can substitute for other database objects, let's see how to use them in practice.