How to Use INSTEAD OF Triggers for Custom SQL Logic

Trigger Types in SQL

• Data Manipulation Language trigger (DML)

  • It's type of special Stored Procedure that automatically execute action (INSERT, UPDATE or DELETE) in a table or view.

• Data Definition Language trigger (DDL)

  • It's type of triggers that perform administrative tasks, like CREATE, ALTER, DROP or DDL

• Logon trigger

DML Trigger Types

• INSTEAD OF trigger

• AFTER trigger

The difference between them is the implementation of fire. We will show the demo later.

Benefits for using DML Trigger

• Enhance or maintain data integrity

  • Check constraints

  • Auditing

  • Default value

  • Referential

Trigger evaluates the state of a table or view before and after the modification. It's treated as a single transaction. What does this mean? It means that if an error occurs, the entire transaction, including the SQL statement that started the trigger, will be rolled back. You can also have multiple triggers, each with a specific action, defined in the order if you want to capture modification values.

Virtual Table

Before we write logic inside the trigger SQL statement, we need to know which data or values have changed. This source is called a virtual table.

A virtual table is a temporary snapshot* of a table that the database creates to hold the data being inserted, updated, or deleted. These virtual tables are named INSERTED and DELETED. Both are available for INSTEAD OF and AFTER trigger.

• INSERTED Table: This virtual table stores a duplicate of the impacted rows during an INSERT or UPDATE operation. For an INSERT operation, it holds the new rows that will be added. For a UPDATE action, it holds the rows' new values following the modification.

• DELETED Table: This virtual table stores a replica of the impacted rows during a DELETE or UPDATE action. For a DELETE operation, it contains the rows that are being removed. For an UPDATE operation, it contains the old values of the rows before the modification.

INSTEAD OF Trigger

In MS SQL, an "INSTEAD OF" trigger is a type of trigger that is executed in place of the triggering action (INSERT, UPDATE, or DELETE). This allows you to override the default behavior of these actions and provide custom logic. However, it DOES NOT execute the statements where trigger was fired from.

Here are some examples:

Example 1: INSTEAD OF INSERT Trigger

Suppose you have an Employee table, and you want to insert data in it.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FullName VARCHAR(50),
    Department VARCHAR(50),
    Designation VARCHAR(50),
    HireDate DATE,
    AnnualSalary DECIMAL(10, 2)
);

INSERT INTO Employees (EmployeeID, FullName, Department, Designation, HireDate, AnnualSalary) VALUES
(1, 'John Doe', 'HR', 'Manager', '2020-01-15', 75000.00),
(2, 'Jane Smith', 'IT', 'Developer', '2019-03-22', 85000.00),
(3, 'Michael Brown', 'Marketing', 'Coordinator', '2021-07-30', 65000.00),
(4, 'Emily Davis', 'Finance', 'Analyst', '2018-11-05', 70000.00),
(5, 'David Wilson', 'Sales', 'Sales Executive', '2022-06-18', 60000.00);
GO

CREATE TRIGGER InsteadOfInsertEmployees
ON Employees
INSTEAD OF INSERT
AS
BEGIN
    -- Example: Modify data before insertion
    INSERT INTO Employees (EmployeeID, FullName, Department, Designation, HireDate, AnnualSalary)
    SELECT EmployeeID, FullName, Department, Designation, HireDate, AnnualSalary + 10000 
    -- Increase salary by 10000
    FROM inserted;
END;

-- Insert one row
INSERT INTO Employees (EmployeeID, FullName, Department, Designation, HireDate, AnnualSalary) VALUES
(6, 'Mary Lou', 'DBA', 'Database Executive', '2024-06-18', 10000.00);

Outcome: The trigger modified the inserted record. I inserted a salary of $10,000. The trigger detected this and increased it by $10,000. That's why the final value is $20,000.

I am not making this article hard to read. The INSTEAD OF UPDATE and INSTEAD OF DELETE syntax are similar to the INSTEAD OF INSERT syntax. However, I want to point out two things. Remember what I mentioned earlier:

This allows you to override the default behavior of these actions and provide custom logic.

For example, an AFTER DELETE Trigger would normally execute the DELETE logic by default. But if I use INSTEAD OF DELETE and change the logic, what would happen?

;CREATE TRIGGER InsteadOfDeleteEmployees ON Employees
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON
    UPDATE ep
    SET ep.AnnualSalary = 0
    FROM Employees ep
    INNER JOIN deleted as d
    ON ep.EmployeeID = d.EmployeeID
END;
GO

DELETE FROM Employees WHERE EmployeeID = 1;

I replaced "DELETE" with "UPDATE" in the INSTEAD OF DELETE trigger code and ran the "DELETE" statement. The record remained, but an "UPDATE" was performed instead.

WITH CHECK option

The WITH CHECK option is not applicable for a view that is intended to use an INSTEAD OF trigger.

If you create a trigger based on a VIEW with the "WITH CHECK" option, an error will occur before you run it.

DROP TABLE Address;
DROP TABLE Employees;
DROP VIEW EmployeeView;
GO

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FullName VARCHAR(50),
    Department VARCHAR(50),
    Designation VARCHAR(50),
    HireDate DATE,
    AnnualSalary DECIMAL(10, 2)
);
GO

INSERT INTO Employees (EmployeeID, FullName, Department, Designation, HireDate, AnnualSalary) VALUES
(1, 'John Doe', 'HR', 'Manager', '2020-01-15', 75000.00),
(2, 'Jane Smith', 'IT', 'Developer', '2019-03-22', 85000.00),
(3, 'Michael Brown', 'Marketing', 'Coordinator', '2021-07-30', 65000.00),
(4, 'Emily Davis', 'Finance', 'Analyst', '2018-11-05', 70000.00),
(5, 'David Wilson', 'Sales', 'Sales Executive', '2022-06-18', 60000.00);
GO

CREATE TABLE Address (
    AddressID INT PRIMARY KEY,
    EmployeeID INT,
    Address NVARCHAR(255),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);
GO
INSERT INTO Address (AddressID, EmployeeID, Address)
VALUES(1,1,'Carlifornia'), (2,2,'Florida');
GO

;CREATE VIEW ViewWithCheckOption AS
SELECT e.EmployeeID, e.FullName, a.address,  e.AnnualSalary 
FROM Employees e
JOIN Address a on e.EmployeeID = a.EmployeeID
WITH CHECK OPTION;
GO

;CREATE VIEW ViewWithOutCheckOption AS
SELECT e.EmployeeID, e.FullName, a.address,  e.AnnualSalary 
FROM Employees e
JOIN Address a on e.EmployeeID = a.EmployeeID
GO

INSERT INTO ViewWithOutCheckOption (EmployeeID, FullName, AnnualSalary) VALUES
(1, 'Mary Lou', 10000.00);
SELECT * FROM ViewWithOutCheckOption
GO

INSERT INTO ViewWithCheckOption (EmployeeID, FullName, AnnualSalary) VALUES
(7, 'Mary Lou 2', 10000.00);
SELECT * FROM ViewWithOutCheckOption
GO

Conclusion

This article covers a part of DML INSTEAD OF triggers. It explains the types of INSTEAD OF triggers, their benefits, and limitations, such as enhancing data integrity and auditing capabilities. I also briefly mentioned that virtual tables( INSERTED and DELETED) function within triggers and included examples of creating and using an INSTEAD OF trigger in MS SQL.