SQL Server Objects: Triggers

A few years ago, during a job interview, I was presented with a scenario where the idea was to perform certain actions upon data being entered into a table. It was not as straight forward as simply being a matter of adding a record (that's when foreign keys would have sufficed), but involved something a tad more complicated.

In any case, I answered that I would use a Trigger to accomplish this particular task, which led to some snickering across the table. This caught me off-guard, so I enquired what was so funny. As it turns out, the interviewer worked with a guy in the past, and that guy's standard answer to any question was always a Trigger.

Triggers are not some magical database object, and in my mind, actually do come with some disadvantages, but they have their uses.

Definition:
A trigger is a piece of code that is executed when triggered (hence the name) by certain events. It basically is a similar to a stored procedure that hooks to event types defined on objects (for instance, a table).

Types:

• AFTER - After triggers execute after the event has been completed. I.e. a record is inserted, and after the insert (including possible actions in related tables), the trigger fires.
• FOR - Identical to After triggers. FOR triggers have essentially been replaced with AFTER triggers.
• INSTEAD OF - Instead Of triggers will perfrom a different action than the one intended by the code that triggered the event. As an example, instead of performing a deletion, the trigger will mark a record as inactive by updating the Status column to 0.

The different types of triggers can fire on each of the following events:

Events:

• INSERT - The inserting of a record.
• UPDATE - The update of a record.
• DELETE - The deletion of a record.

While a trigger is used, two special tables are exposed: "INSERTED" and "DELETED". INSERTED stores information about inserted and updated records affected by the trigger, while DELETED does the same for deleted and updated data (An update essentially is a delete followed by an insert. Records will first be stored into the DELETED table, and then added to the INSERTED table). These tables allow the developer control over affected records, for instance for roll-back purposes, or to enforce referential integrity.

Disadvantages:
As mentioned, using triggers is not without risks. They are invoked every time for the event they are hooked on. In busy systems, unless the trigger is properly coded, this could affect performance. Triggers are typically quite fast, but even fast executing code, if executed enough times, can cause issues.

Another disadvantage is that triggers are not typically code one thinks of when troubleshooting performance. This was even more true in SQL Server 2000, where triggers were far more hidden than nowadays in the SQL Server Management console (where they take up a spot under the table or view they are defined on).