Views and Triggers in SQL Server

Views and triggers are database objects that simplify your workflow and automate behavior. Views act as virtual tables built from queries, while triggers execute T-SQL code automatically in response to data changes. Both are powerful tools for maintaining clean, consistent databases.

Creating Views

A view encapsulates a SELECT query behind a name, making complex queries reusable and hiding underlying table complexity from consumers.

CREATE VIEW vw_ActiveEmployees AS
SELECT
    e.EmployeeID,
    e.FirstName + ' ' + e.LastName AS FullName,
    d.DepartmentName,
    e.Salary,
    e.HireDate
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE e.IsActive = 1;

-- Use the view like a table
SELECT * FROM vw_ActiveEmployees
WHERE DepartmentName = 'Engineering'
ORDER BY HireDate DESC;

Views with SCHEMABINDING

WITH SCHEMABINDING binds the view to the schema of the underlying tables, preventing changes that would break the view. This is required for indexed views.

CREATE VIEW vw_OrderSummary
WITH SCHEMABINDING
AS
SELECT
    o.CustomerID,
    COUNT_BIG(*) AS OrderCount,
    SUM(o.OrderTotal) AS TotalSpent
FROM dbo.Orders o
GROUP BY o.CustomerID;

Updating Data Through Views

Simple views that map to a single table can be updated directly. Views with JOINs, aggregates, or DISTINCT are generally read-only unless you use INSTEAD OF triggers.

CREATE VIEW vw_ProductPrices AS
SELECT ProductID, ProductName, Price
FROM Products
WHERE IsActive = 1;

-- This update goes through to the base table
UPDATE vw_ProductPrices
SET Price = 39.99
WHERE ProductID = 101;

AFTER Triggers

An AFTER trigger fires after an INSERT, UPDATE, or DELETE operation completes. It accesses the INSERTED and DELETED virtual tables to see the affected rows.

CREATE TRIGGER trg_Employees_AuditUpdate
ON Employees
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO EmployeeAuditLog (EmployeeID, FieldChanged, OldValue, NewValue, ChangedAt)
    SELECT
        i.EmployeeID,
        'Salary',
        CAST(d.Salary AS NVARCHAR(50)),
        CAST(i.Salary AS NVARCHAR(50)),
        GETDATE()
    FROM INSERTED i
    INNER JOIN DELETED d ON i.EmployeeID = d.EmployeeID
    WHERE i.Salary <> d.Salary;
END;

INSTEAD OF Triggers

An INSTEAD OF trigger replaces the original operation entirely. This is commonly used to make complex views updatable or to implement custom validation logic.

CREATE TRIGGER trg_Products_SoftDelete
ON Products
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- Soft delete instead of actual deletion
    UPDATE Products
    SET IsActive = 0, DeletedAt = GETDATE()
    WHERE ProductID IN (SELECT ProductID FROM DELETED);
END;

Managing Triggers

You can enable, disable, or drop triggers as needed. Disabling a trigger temporarily stops it from firing without deleting the definition.

-- Disable a trigger
DISABLE TRIGGER trg_Employees_AuditUpdate ON Employees;

-- Re-enable it
ENABLE TRIGGER trg_Employees_AuditUpdate ON Employees;

-- Drop a trigger
DROP TRIGGER IF EXISTS trg_Products_SoftDelete;

Views simplify access patterns and provide a layer of abstraction over your schema. Triggers automate auditing, validation, and derived data maintenance. Use both judiciously -- overusing triggers can make debugging difficult since they execute implicitly.