Triggers are a powerful feature in SQL Server that allow you to automatically perform actions when certain events occur, such as inserting, updating, or deleting data in a table. However, triggers can be misunderstood and misused, leading to potential issues and performance problems. In this article, we will explore the different types of triggers and best practices for coding and using them effectively.

Types of Triggers

SQL Server provides two types of DML (Data Manipulation Language) triggers: AFTER and INSTEAD OF. AFTER triggers are fired after the action has taken place, while INSTEAD OF triggers are fired instead of the modification being made. It’s important to note that triggers are only fired when a data modification statement (UPDATE, INSERT, DELETE) is issued, and there are no triggers for SELECT statements.

AFTER triggers are commonly used to log actions to an audit or logging table. They are part of the transaction, so any error within the trigger will cause the entire transaction to fail and rollback.

INSTEAD OF triggers, on the other hand, are Microsoft’s answer to BEFORE triggers available in other RDBMS systems. They allow you to modify the data before it is inserted, updated, or deleted. It’s important to ensure that the modification is completed within the trigger, otherwise, the action will not take place.

Coding Triggers

When coding triggers, it’s crucial to understand that triggers deal with sets of data, not individual rows. One common mistake is using variables incorrectly within a trigger, which can lead to the trigger only handling a single row update. To handle any update, it’s recommended to use set-based operations instead of individual row operations.

For example, instead of using a variable to store the ContactId and checking its value, you can use the EXISTS clause to check if any rows meet the condition. This ensures that the trigger handles any update correctly, regardless of the order of the update.

CREATE TRIGGER Person.Contact_Update ON Person.Contact AFTER UPDATE AS
IF EXISTS (SELECT * FROM inserted WHERE ContactId > 10)
BEGIN
    INSERT INTO Person.ContactLog (ContactID, Action)
    SELECT ContactID, 'UPDATE' FROM inserted WHERE ContactId > 12
END

It’s worth mentioning that using loops (cursors) in triggers should be avoided whenever possible, as they can significantly impact performance. If you find yourself needing to process each row individually, it’s recommended to re-evaluate your process and consider a set-based option.

Outside Actions in Triggers

Triggers should primarily focus on database-related actions and not perform actions outside the database engine, such as sending emails or manipulating files. Any action that takes place outside the database should be handled separately, either through a job or a Windows service.

Performing external actions within a trigger can lead to potential issues, as the trigger is part of a transaction. Any errors within the trigger will cause the entire transaction to fail and rollback, including the insert, update, or delete operation that triggered the trigger. By separating the external actions from the trigger, you can ensure that the main transaction completes successfully without any hidden complexities.

Conclusion

Triggers are a powerful tool in SQL Server that can automate actions based on data modifications. However, it’s important to understand the different types of triggers and how to code them effectively. By following best practices and avoiding common pitfalls, you can leverage triggers to enhance the functionality and maintain the integrity of your database.

Resources:

• Trigger Trivia by Andy Warren
• Trouble with Triggers blog entry by Conor Cunningham
• Triggers…Evil blog entry by Louis Davidson
• SQL BOL