When you create a trigger in SQL Server, you have the option of firing it in conjunction with the triggering statement (i.e. the SQL statement that fired the trigger), or firing it instead of that statement.

To fire the trigger instead of the triggering statement, use INSTEAD OF argument.

This is in contrast to using the FOR or AFTER arguments. When you use those arguments, the trigger fires only when all operations specified in the triggering SQL statement have launched successfully.

Example

Create a sample table:

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

Create the trigger:

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

Insert a sample row:

INSERT INTO t1 (c1, c2, c3) 
VALUES (1, 1, 1);

SELECT * FROM t1;

Here’s what we have so far:

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

Now let’s run an UPDATE statement against the table (this will fire the trigger).

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

SELECT * FROM t1;

Result:

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

As expected, the UPDATE statement in the triggering statement was replaced with the one in the trigger.

My trigger specified that any time there’s an attempted update to the table, update the c3 column instead.

Run Only When a Specific Column is Updated

You can also use the UPDATE() function to specify code to run only when a specified column is updated.

For example, we could alter our trigger as follows:

ALTER TRIGGER trg_t1
ON t1
INSTEAD OF UPDATE
AS
IF ( UPDATE(c1) )
BEGIN
UPDATE t1
SET c3 = c3 + 1
WHERE id IN (SELECT DISTINCT id FROM inserted)
END;

Now run the previous UPDATE statement again:

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

SELECT * FROM t1;

Result:

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

Again, the c3 column is incremented.

But, now let’s try to update the c2 column:

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

SELECT * FROM t1;

Result:

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

Nothing changes. The c3 column remains the same.

Not even the c2 column is updated. This is because, the trigger still runs instead of the triggering statement.

Run Trigger Instead of DELETE

We can modify the trigger to run instead of any DELETE statements.

ALTER TRIGGER trg_t1
ON t1
INSTEAD OF DELETE
AS
UPDATE t1
SET c3 = c3 + 1
WHERE id IN (SELECT DISTINCT id FROM deleted);

Now let’s try to delete all rows and then select all rows from the table.

DELETE FROM t1;

SELECT * FROM t1;

Result:

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

Note that for this trigger to work properly, I had to query the deleted table in my trigger (as opposed to the inserted table in the previous examples).

These two tables are created and managed by SQL Server.

The deleted table 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.

The inserted table 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.

Some Restrictions to Keep in Mind

You can define a maximum of one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement on a table or view.

You can’t define INSTEAD OF triggers on updatable views that use WITH CHECK OPTION.