A trigger is like a stored procedure that automatically (and atomically) executes on a given condition, for instance when you insert a record into a table. Used properly, this is a powerful tool to enforce business rules, perform automated calculations, log changes, and more. But triggers also come with some potential pitfalls.

DML triggers (as opposed to DDL triggers, logon triggers)

This article will concentrate only on DML triggers (i.e. triggers that fire on changes in records). DDL triggers, which fire on changes in the schema, and logon triggers are subjects worthy of a separate, dedicated article.

CREATE TRIGGER basics

The basic CREATE TRIGGER syntax looks a lot like CREATE PROCEDURE, except you also need to define what table the trigger belongs to, and what condition it fires on.

CREATE TRIGGER dbo.myTable_ins_trigger
ON dbo.myTable
FOR INSERT, UPDATE
AS

--- Log changes to an auditing table
INSERT INTO dbo.myAuditTrail (who, when, what, rows)
SELECT SUSER_SNAME(), GETDATE(), 'myTable', @@ROWCOUNT;

GO

Valid trigger actions are INSERT, UPDATE and DELETE, or a combination of several, separated by commas.

The code in the trigger fires immediately after the trigger action (in the example above, an INSERT or UPDATE operation) has completed, but within the same transaction. This means that if the trigger uses ROLLBACK TRANSACTION or crashes, the initial statement that fired the trigger will also be rolled back.

The full syntax can be found in the MSDN article on CREATE TRIGGER.

Enabling and disabling triggers

Triggers can be enabled or disabled with the ALTER TABLE statement, like this:

ALTER TABLE dbo.myTable DISABLE TRIGGER myTable_ins_trigger;

ALTER TABLE dbo.myTable ENABLE TRIGGER myTable_ins_trigger;

You can also use the ALL keyword instead of a trigger name to enable/disable all of the triggers on a table in question.

ALTER TABLE dbo.myTable DISABLE TRIGGER ALL;

ALTER TABLE dbo.myTable ENABLE TRIGGER ALL;

Using the inserted and deleted special tables

Inside every trigger you have access to two special tables, inserted and deleted. These tables share the same column schema as the trigger’s base table, and they contain all the records that have been “touched” by the operation that fired the trigger.

If you INSERTed records, those records can be found in the inserted table, and if you DELETEd rows, those rows can be found in the deleted table. For UPDATEs, the deleted table will contain the row data before the UPDATE, inserted will reflect the data after. If you have a primary key on the base table, you can join deleted and inserted to see which rows and columns were updated.

Triggers are set based!

This is important: INSERTs, UPDATEs and DELETEs can affect multiple rows. Because of this, always write triggers that are able to handle multiple rows.

On some database platforms, notably Oracle, triggers are row-based, and run once for every affected row. On SQL Server, a trigger will fire once for every statement that is run on the table, regardless of how many rows were touched or changed. This obviously comes with performance benefits because the trigger code will inherently be set based (it’s T-SQL, after all).

You should always plan and optimize for statements that affect multiple rows, making good use of the inserted and deleted tables, so you won’t have to resort to cursors or variables. Remember, other people using your database may not even know about your trigger in the first place.

INSTEAD OF triggers

As we saw above, the most common type of trigger fires after a statement has executed. There is, however, another type of trigger that comes in very useful at times. An INSTEAD OF trigger, like the name sounds, runs instead of the statement that fired it. The data that the statement modified is still available in the deleted and inserted tables, but no data is changed in the trigger’s parent table. This is very useful if you want to check or wash any data before it is saved (and potentially violates any constraints or business rules). The syntax is very similar to a “regular” AFTER trigger:

CREATE TRIGGER dbo.myTable_ins_trigger
ON dbo.myTable
INSTEAD OF INSERT, UPDATE
AS

--- Log changes to an auditing table
INSERT INTO dbo.myAuditTrail (who, when, what, rows)
SELECT SUSER_SNAME(), GETDATE(), 'myTable', @@ROWCOUNT

GO

Triggers on views

A great way to use INSTEAD OF triggers is when they are applied to views. Since views normally cannot be updated, you can’t place AFTER triggers on them. However, you can give a view an INSTEAD OF trigger that catches any INSERTs, UPDATEs and/or DELETEs that you execute on the view.

Writing a view and giving it INSTEAD OF triggers is a really powerful abstraction pattern that will allow you to build an “interface”, if you will, to let users modify data. Your code in the trigger can then properly validate and store this data in the different tables that the view uses.

Recursive triggers

Obviously, a trigger can be written to fire a statement against its own parent table, and this could lead to recursion. Always keep this in mind when developing triggers, so you don’t end up with an “infinite” recursion of triggers calling triggers calling triggers (actually, there is a maximum nesting level, at which SQL Server will break your statement).

Also, there is a database option to disable recursive triggers, but this is not as good practice as making sure in the first place that your triggers don’t recurse. Also, simply disabling trigger recursion in the database only prevents direct recursion (one trigger firing itself), not indirect recursion (when two triggers fire each other). And remember, you may actually want your trigger to make a recursion in some circumstances.

To check the current level of recursion in T-SQL, use the @@NESTLEVEL variable.

Another thing you may want to check at the very beginning of the trigger is if this update affects any rows at all – remember, the trigger will fire once per statement, whether there are any rows affected or not. Use the @@ROWCOUNT variable to count the affected rows:

IF (@@ROWCOUNT=0) RETURN

Returning result sets from triggers will be deprecated

Like in a stored procedure, you can return result sets from a trigger using a SELECT statement. However, avoid this, as this possibility will be deprecated in a future version of SQL Server. And, besides, it’s not very pretty. If you want to retrieve the results of an operation, you can probably accomplish this with the OUTPUT clause.

Conclusion

Triggers are a powerful tool to validate and manage data updates, but there are quite a few pitfalls that you have to watch out for. Let me know in the comments section below if there’s anything important I’ve left out, or if there’s something I haven’t been clear about.