How do you use the INSTEAD OF trigger on a view to make it updatable?
QuinnLw
Posted by QuinnLw
Last Updated: June 08, 2024
trigger view
In SQL, an INSTEAD OF trigger is commonly used on views to make them updatable. Normally, views can be non-updatable if they involve complex queries like joins, aggregations, or group by clauses. By using an INSTEAD OF trigger, you can define how to handle INSERT, UPDATE, or DELETE operations on the view and redirect those operations to the underlying base tables. Here’s a general approach to create an INSTEAD OF trigger on a view:
Steps to Create an INSTEAD OF Trigger
1. Create the Base Tables: Define the tables that the view is based on. 2. Create the View: Define a view that aggregates or selects from these tables in a way that may not be directly updatable. 3. Create the INSTEAD OF Trigger: Write the trigger that specifies how to handle inserts, updates, and deletes.
Example Scenario
Let’s say you have two tables: Employees and Departments. You want to create a view called EmployeeView that includes selected fields but only allows updates through this view.
Step 1: Create Base Tables
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    DepartmentID INT
);

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName NVARCHAR(100)
);
Step 2: Create a View
CREATE VIEW EmployeeView AS
SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID;
Step 3: Create the INSTEAD OF Trigger
CREATE TRIGGER trg_InsteadOfUpdate ON EmployeeView
INSTEAD OF UPDATE
AS
BEGIN
    -- Update Employees table based on the incoming data
    UPDATE e
    SET e.Name = i.Name,
        e.DepartmentID = d.DepartmentID
    FROM Employees e
    INNER JOIN inserted i ON e.EmployeeID = i.EmployeeID
    INNER JOIN Departments d ON d.DepartmentName = i.DepartmentName;
END;
In this trigger: - The inserted table contains the new values that would have been applied to the view. - The trigger fetches corresponding records from the Employees table to perform the update. - It assumes the department name exists and matches to update DepartmentID accordingly.
Example of Using the View
To update a record using the view, you just run: 
UPDATE EmployeeView
SET Name = 'John Doe', DepartmentName = 'IT'
WHERE EmployeeID = 1;
This will trigger the INSTEAD OF logic defined, and the updates will be applied accordingly in the Employees and possibly Departments table.
Important Considerations
- DELETE Trigger: Similarly, you can create an INSTEAD OF DELETE trigger to handle deletions from the view. - INSERT Trigger: You can also create an INSTEAD OF INSERT trigger if you want to manage insertions through the view. - Error Handling: Ensure to handle errors (e.g., when a given DepartmentName does not exist). - Performance: Keep in mind that using triggers can introduce overhead, so be cautious with complex operations. Using INSTEAD OF triggers effectively allows you to maintain the logical separation of concerns, allowing views to remain abstracted while still providing functionalities for CRUD operations.
Default Image