Triggers are a powerful feature in SQL Server that allow you to automate actions based on insert, update, or delete statements. In this article, we will explore some tips and tricks to help you make the most out of triggers in your SQL Server databases.

Tip #1 – Disabling Triggers Instead of Dropping Them

There may be situations where you want to avoid the overhead of a trigger, such as when performing a large number of updates to a table. Instead of dropping the trigger, you can disable it using the syntax ‘ALTER TABLE TABLENAME DISABLE TRIGGER TRIGGERNAME’. Disabling a trigger is an elegant way to temporarily bypass its functionality without affecting other users.

Tip #2 – Selectively Bypassing Triggers

If you need to perform a large number of updates during the day but still rely on triggers to handle work done by non-admin users, you can selectively bypass a trigger. By adding a few lines of code to the top of the trigger, you can specify certain machines as admin kiosks that can perform operations without triggering the associated triggers.

Tip #3 – Triggers Fire per Batch, Not per Row

Triggers are set-based operations, meaning they fire once per batch rather than per row. If you run an update statement that modifies multiple rows, the update trigger will only fire once. Keep this in mind when writing trigger logic that references the logical inserted or deleted tables, as it needs to handle multiple rows.

Tip #4 – Avoid Returning Result Sets from Triggers

While triggers can return result sets if you have a standalone select statement within them, it is generally not recommended. Returning a result set to an application that is not expecting one can potentially cause issues. It is good practice to use ‘SET NOCOUNT ON’ to avoid sending back the rows affected message as well. If you want to ensure no result sets occur, you can use ‘sp_configure’ to set ‘disallow results from triggers’ to ‘on’.

Tip #5 – Triggers Are Logically Part of the Transaction

Although triggers are executed after the triggering statement, any actions performed within the trigger are treated as part of the transaction. For example, you can create a delete trigger that contains a ‘ROLLBACK’ statement to prevent deletes. This ensures that the trigger’s actions are rolled back if the transaction is rolled back.

Tip #6 – Triggers Execute with the Permissions of the User

Triggers execute with the permissions of the user who triggered the action. This is important to consider when using triggers for auditing purposes. For example, if a regular user deletes a row, they may not have insert permission to the audit table where the deleted row is being inserted. Ensure that the necessary permissions are granted to avoid any issues.

Tip #7 – Multiple Triggers of the Same Type on a Table

You can have multiple triggers of the same type (e.g., insert, update, delete) on a table. This can be useful when you need to add custom behavior to an off-the-shelf application that already has its own triggers installed. By creating additional triggers or modifying existing ones, you can separate your code from the application’s code. Keep in mind that the execution order of the triggers can only be partially controlled by setting the first and last triggers.

Tip #8 – Recursive Triggers

Triggers can be called recursively, but it is generally recommended to use this technique sparingly. Recursive triggers can be difficult to troubleshoot and can increase the duration of transactions. It is best to carefully consider the need for recursion and explore alternative solutions if possible.

Tip #9 – Instead Of Triggers

Instead Of triggers allow you to check or change data before check constraints fire. They can also be applied to views, enabling you to perform actions that would otherwise be impossible on non-updatable views. Instead Of triggers are essentially “before triggers” that give you more control over the data manipulation process.

Tip #10 – DDL and CLR Triggers

In SQL Server 2005 and later versions, you have the additional options of DDL triggers and CLR triggers. DDL triggers allow you to inspect and act on DDL changes committed on the server, making them useful for auditing or enforcing naming conventions. CLR triggers work similarly to regular triggers but provide the power and flexibility of using the Common Language Runtime (CLR) within SQL Server.

Triggers are a valuable tool in SQL Server that can automate actions and enhance the functionality of your databases. However, it is important to use them carefully and consider the potential impact on performance and maintainability. By following these tips and tricks, you can make the most out of triggers in your SQL Server environment.