# 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.
    

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">The virtual tables <code>INSERTED</code> and <code>DELETED</code> are only available during the execution of the logic trigger statement. They are also only available within the straight code section. What does this mean? If you call a <code>stored procedure</code> during trigger code, it will be unable to access the virtual table.</div>
</div>

---

### 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 ***<mark>override</mark>*** the default behavior of these actions and provide custom logic. However, it DOES NOT execute the statements where trigger was fired from.

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">The <code>WITH CHECK</code> option is not applicable for a view that is intended to use an INSTEAD OF trigger.</div>
</div>

Here are some examples:

### **Example 1: INSTEAD OF INSERT Trigger**

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

```sql
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
```

```sql
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.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1723503349053/62b9d2ed-5552-44fe-9b96-e7800687c11a.png align="left")

---

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 ***<mark>override</mark>*** 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?

```sql
;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;
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1723493387809/9d4f870e-8ab7-4462-8562-b5ea58ea4ae7.png align="left")

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.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1723508591063/6182d990-7a2a-4723-82ff-b3e769d5a07a.png align="left")

```sql
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
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1723508762859/ccbb81ba-97eb-4cb8-9e79-9aa919b4d2df.png align="left")

## 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.

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">Please see another article for <code>AFTER</code> Trigger.</div>
</div>