How to Use INSTEAD OF Triggers in SQL Server 2012

In SQL Server 2012, triggers are special types of stored procedures that automatically execute in response to certain events on a particular table or view. Among the types of triggers available, INSTEAD OF triggers are especially useful when you want to override the standard behavior of INSERT, UPDATE, or DELETE operations.

This tutorial will walk you through the concept, syntax, use cases, and examples of INSTEAD OF triggers in SQL Server 2012.

What is an INSTEAD OF Trigger?

An INSTEAD OF trigger executes in place of the triggering action. That means:

  • If someone tries to INSERT, UPDATE, or DELETE data in a table, the INSTEAD OF trigger will run before the actual operation and can prevent or modify it.
CREATE TRIGGER trigger_name
ON table_name
INSTEAD OF [INSERT | UPDATE | DELETE]
AS
BEGIN
-- Trigger logic here
END

Example 1: INSTEAD OF INSERT

Let’s say we have a table called Employees:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Salary MONEY
);

Now, we want to prevent inserting a new employee if their salary is below $1000.

CREATE TRIGGER trg_Employees_Insert
ON Employees
INSTEAD OF INSERT
AS
BEGIN
    IF EXISTS (SELECT * FROM inserted WHERE Salary < 1000)
    BEGIN
        RAISERROR ('Salary must be at least $1000.', 16, 1);
        RETURN;
    END

    INSERT INTO Employees (EmployeeID, Name, Salary)
    SELECT EmployeeID, Name, Salary FROM inserted;
END

This trigger will block the insertion and raise an error if the salary is too low.

Example 2: INSTEAD OF UPDATE

Let’s say you want to prevent updating the EmployeeID field:

CREATE TRIGGER trg_Employees_Update
ON Employees
INSTEAD OF UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT * FROM inserted i
        JOIN deleted d ON i.EmployeeID <> d.EmployeeID
    )
    BEGIN
        RAISERROR ('Cannot change EmployeeID.', 16, 1);
        RETURN;
    END

    UPDATE Employees
    SET Name = i.Name,
        Salary = i.Salary
    FROM Employees e
    JOIN inserted i ON e.EmployeeID = i.EmployeeID;
END

Example 3: INSTEAD OF DELETE

Let’s log deleted employees instead of removing them.

CREATE TABLE DeletedEmployees (
    EmployeeID INT,
    Name NVARCHAR(100),
    Salary MONEY,
    DeletedAt DATETIME
);

CREATE TRIGGER trg_Employees_Delete
ON Employees
INSTEAD OF DELETE
AS
BEGIN
    INSERT INTO DeletedEmployees (EmployeeID, Name, Salary, DeletedAt)
    SELECT EmployeeID, Name, Salary, GETDATE() FROM deleted;

    DELETE FROM Employees WHERE EmployeeID IN (SELECT EmployeeID FROM deleted);
END

Notes and Best Practices

  • INSTEAD OF triggers are especially useful on views (since views can’t have direct DML operations).
  • Use inserted and deleted pseudo-tables to access the rows being affected.
  • Always test triggers carefully—unexpected behavior can arise if not handled properly.
  • Keep trigger logic as simple as possible to avoid performance overhead.

INSTEAD OF triggers in SQL Server 2012 allow you to take full control over what happens during INSERT, UPDATE, and DELETE operations. They’re powerful tools for enforcing business rules, validating data, and managing complex logic that would otherwise require client-side validation or stored procedures.