What is a Trigger?

A trigger is a specialized stored procedure which fires automatically when an alteration occurs to an underlying table due to an action like deleting, inserting, or updating of a row, or a batch of rows, within the single table that the stored procedure is linked with, or potentially multiple tables if the trigger is linked to a view. To clarify the statement of ‘batch of rows’– if we have an UPDATE statement that modifies several rows, for example, then the trigger will not fire for each row updated, but only once when the batch completes. If we needed to fire the trigger for each row being updated, then we would need to construct some sort of loop processing within our T-SQL to do this.

Triggers can be assigned to tables or views. However, although there are two types of triggers, INSTEAD OF and AFTER , as we will see later in the Types of Triggers section, only one type of trigger can be assigned to views. Briefly, an INSTEAD OF trigger is the one that is usually associated with a view, and runs on an UPDATE action placed on that view. An AFTER trigger fires after a modification action has occurred.

It is possible to have more than one trigger firing for the same action, if they are AFTER triggers. So it is quite possible and perfectly valid for two or more separate triggers to fire on the same table modification action. This will allow separate processing to take place, rather than having different underlying functionalities in the same procedure. It is also possible to define the order in which triggers will fire. Both these areas are covered in detail, in the Types of Triggers section.

From a performance viewpoint, the fewer the triggers, the better, as we will invoke less processes. Therefore, do not think that having one trigger per action to make things modular will not incur performance degradation. A trigger's main overhead is referencing either two specialist tables that exist in triggers – deleted and inserted or other tables for business rules. Modularizing triggers to make the whole process easier to understand will incur multiple references to these tables, and hence a greater overhead.