In SQL Server, you can use the CREATE TRIGGER statement to create a trigger.

A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server.

You can create a DML trigger, a DDL trigger, or a logon trigger.

This article provides an example of creating a DML trigger.

What is a DML Trigger?

A DML trigger is a trigger that runs when a user tries to modify data through a data manipulation language (DML) event.

DML events include INSERT, UPDATE, or DELETE statements. DML triggers can be used to enforce business rules and data integrity, query other tables, and include complex T-SQL statements.

The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger.

Example

Here’s an example to demonstrate how DML triggers work.

CREATE TABLE t1 (
    id int IDENTITY(1,1) NOT NULL,
    c1 int DEFAULT 0,
    c2 int DEFAULT 0,
    c3 int DEFAULT 0
);

CREATE TRIGGER trg_t1
ON t1
AFTER INSERT, UPDATE
AS
UPDATE t1
SET c3 = c3 + 1
WHERE id IN (SELECT DISTINCT id FROM inserted);

In this example I create a table and I also create a trigger that will be fired whenever a row is inserted or updated in that table.

In this case, the trigger adds 1 to the c3 column whenever data is inserted or updated.

I called the trigger trg_t1. I followed that part with ON t1, which means that the trigger will be run on the table called t1.

You can alternatively specify a view for the trigger to run on, although you can only reference a view by an INSTEAD OF trigger (in this case, replace AFTER with INSTEAD OF). Also, you can’t define DML triggers on local or global temporary tables.

AFTER specifies that the DML trigger fires only when all operations specified in the triggering SQL statement have launched successfully. You can alternatively specify FOR here.

Another alternative is to use INSTEAD OF, which will run the trigger instead of the triggering SQL statement. This therefore overrides the actions of the triggering statements. 

What’s the inserted Table?

In my trigger, I’m able to find out which row was updated by querying the inserted table.

SQL Server creates and manages a table called inserted, which is a  temporary, memory-resident table that stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

SQL Server also creates and maintains a similar table called deleted, which stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table.

Run the Trigger

Now that the table and its trigger have been created, let’s run some SQL statements that will trigger it.

INSERT INTO t1 (c1) 
VALUES (1);

SELECT * FROM t1;

Result:

+------+------+------+------+
| id   | c1   | c2   | c3   |
|------+------+------+------|
| 1    | 1    | 0    | 1    |
+------+------+------+------+

So we can see that the trigger worked as expected. When I inserted a row, I only specified a value for the c1 column, but the trigger ensured that the c3 column was also updated.

Note that the default value for all columns is 0 (as specified when I created the table), and the trigger added 1 to that.

Let’s perform an UPDATE operation on the same column.

UPDATE t1 
SET c1 = c1 + 1
WHERE id = 1;

SELECT * FROM t1;

Result:

+------+------+------+------+
| id   | c1   | c2   | c3   |
|------+------+------+------|
| 1    | 2    | 0    | 2    |
+------+------+------+------+

Once again, the c3 column was also updated by the trigger.

Now let’s update the c2 column.

UPDATE t1 
SET c2 = c2 + 1
WHERE id = 1;

SELECT * FROM t1;

Result:

+------+------+------+------+
| id   | c1   | c2   | c3   |
|------+------+------+------|
| 1    | 2    | 1    | 3    |
+------+------+------+------+

So once again, the c3 column is updated by the trigger.

This particular trigger is fired whenever any other column in the same row is updated.

You can also use IF UPDATE(column_name) to check for an update to a single column, or COLUMNS_UPDATED() to check for updates across multiple columns.