TechInitiative

SQL Triggers 101: Examples and when to use

Triggers are really stored procedures scheduled to execute in place of or after data modifications. They can be extremely useful; however, you should be careful of their usage due to system performance.

In my experience, triggers are used when an application requires a modification that was not initially intended for. This could be due to new functionality, poor planning, or maybe a trigger is simply ideal in this situation.

There are three ways to fire a trigger; using SQL commands such as: INSERT, UPDATE, and DELETE. There are only two classes of triggers, INSTEAD OF and AFTER

INSTEAD OF Triggers

INSTEAD OF Triggers will execute your logic, instead of the original logic. For example, if you have a statement like "INSERT INTO dbo.tableName Where (...)", then the existence of a "INSTEAD OF Trigger" will bypass the original insert.

AFTER Triggers

As you can infer, AFTER Triggers will execute after the intended logic executes. In the example below, you will notice the keyword "FOR" is used when executing AFTER triggers, versus the alternative.

It is important to know that triggers are great fixes, and not necessarily best implemented as much as possible. It is naturally more logic, and one must ask themselves, why can this not be done within the original scope of this application?

Example:

CREATE TRIGGER triggerName
ON tableName
AFTER [ INSERT / UPDATE / DELETE ] AS
IF EXISTS (SELECT columnName FROM [ inserted / deleted ])
BEGIN
UPDATE tableName2 SET columnName = value WHERE columnID =
(SELECT columnName FROM tableName INNER JOIN [ inserted | deleted ] ON tableName.columnID = inserted.columnID)
END