Triggers versus Stored Procedure

We may ask why not use a stored procedure instead of a trigger? Well, there may be times when it is the more appropriate choice, as the stored procedure code will work with sets of data at a time, while the trigger will work with rows of data at a time. Therefore, any validation or extra processing when a record is modified can be performed in the trigger, rather than altering the stored procedure to use a cursor, or completing a pass-through of the records at least twice – once for validation and then for an update. It may be possible to have some o f the validation in the JOIN and WHERE statements, but this may not cover every scenario.

In addition, we can get the exact columns that have been modified with a trigger and see the value before or after any column, which can aid in the validation. The main difference between triggers and stored procedures is that stored procedures are controlled and invoked by the user, whereas triggers are controlled totally by the actions performed on the underlying table, and we have no control over their firing except dropping them.

Finally, by using a trigger rather than placing the validation within a stored procedure, we can have the validation or any further script actions in a central place. Therefore, as the database owner, when an ad-hoc query is being executed, or a number of different stored procedures modify the same underlying table, we can be assured that the business validation is being performed.

This is an uncommon, but useful scenario for more complex stored procedures, where we attempt to debug the activities of the stored procedure in the temporary table. If the stored procedure is updating a table at several points within a procedure, we might be able to see which section of the stored procedure has the problem, and the time when the problem occurred, by having an audit trigger.