Tips for using SQL Server 2016 triggers
Try to minimize the number of rows affected in a trigger.
The more number of rows affected in a trigger, the more time a trigger takes to run. So, try to
reduce the number of rows affected in a trigger.
Consider using triggers on memory-optimized tables.
SQL Server 2016 introduces supporting triggers on the memory-optimized tables.
Note. Only triggers that execute after the table operation are supported.
Use cascading referential integrity constraints instead of triggers whenever possible.
For example, if you need to make cascading deletes or updates, specify the ON DELETE or
ON UPDATE clause in the REFERENCES clause of CREATE TABLE or ALTER TABLE statement.
The cascading referential integrity constraints are much more efficient than triggers and can
boost performance.
Try to minimize the number of tables referenced in a trigger.
The more tables referenced in a trigger, the more time a trigger takes to run. So, try to reduce
the number of tables referenced in a trigger.
Consider disabling DML, DDL or logon triggers if you temporary not need them.
When you disable a trigger, the trigger still exists as an object in the current database, but
the trigger does not fire when any Transact-SQL statements on which it was programmed are
executed. You can enable the trigger later by using the ENABLE TRIGGER statement.
Try to minimize the DML trigger’s code size.
Triggers fire during INSERT, UPDATE, or DELETE statements, so the more code that runs in the
trigger, the slower each INSERT, UPDATE, and DELETE that fires it will be.
Use TRY-CATCH logic to trap and handle error conditions in triggers.
SQL Server 2016 has the TRY…CATCH construction, which allows you to make exception handling
like in the C# and C++ languages. Now, you can prevent SQL Server 2016 from raising error
message and you can suppress the error using the TRY…CATCH block.
Consider using change tracking to allow applications to check for data conflicts when
two-way synchronization is required.
Before SQL Server 2008, to allow applications to obtain incremental changes to user tables you
used a combination of triggers and timestamp columns. Now you can accomplish the same goal by
using change tracking feature.
Use DDL triggers for administrative tasks, such as auditing and regulating database operations.
DDL triggers fire stored procedures in response to a variety of DDL statements, such as CREATE,
ALTER, and DROP statements. For example, a DDL trigger created to fire in response to a
DROP TABLE event will do so whenever a DROP TABLE event occurs in the database.
Consider using foreign key constraints instead of triggers to enforce referential integrity.
Because built-in referential integrity is faster, you should use triggers to enforce referential
integrity only when the built-in referential integrity cannot be used.
Use logon triggers to execute the stored procedures in response to a LOGON event.
The LOGON event is raised when a user session is established with an instance of SQL Server 2016.
Logon trigger fires after the authentication phase of logging in finishes, but before the user
session is actually established.
Note. The Logon triggers do not fire if authentication fails.
Consider using the CLR triggers.
You can use any .NET Framework language to create CLR triggers. In CLR triggers you can reference
data in the INSERTED and DELETED tables and access information about database objects affected
by the execution of DDL statements.
Try to avoid roll back in the DML triggers.
Because the ROLLBACK command in a trigger roll backs the entire transaction which invoked the
trigger, try to catch an error before the trigger fires. This can boost performance, because
catching an error early consumes much fewer server resources than letting the trigger roll back.
Consider using event notifications instead of DDL triggers when you use DDL triggers to log
changes or activity occurring on the database.
Event notifications send information about Transact-SQL DDL statements to a service broker service.
Event notifications execute asynchronously and can offer a programming alternative to DDL triggers
and SQL Trace.
Use UPDATE() or COLUMNS_UPDATED() functions inside a trigger when the code in the
trigger affects only certain columns, not all of them.
The UPDATE() function can be used to check only one column at a time. The COLUMNS_UPDATED()
function can be used to check more than one columns at a time. So, if a particular column is
not changed inside a trigger, the code in your trigger will not run. By using these functions,
you can reduce the unnecessary running of code in a trigger and boost total trigger performance.
Try to use CHECK constraints instead of triggers whenever possible.
Constraints are much more efficient than triggers and can boost performance. Constraints are
also more consistent and reliable in comparison with triggers, because you can make errors
when you write your own code to perform the same actions as the constraints. So, you should
use constraints instead of triggers whenever possible.