Understanding Triggers in SQL Server

Triggers in SQL Server are special types of stored procedures that automatically execute or “fire” when certain events occur in the database.

Types of Triggers
 

DML Triggers

These are triggers that fire in response to data manipulation events such as INSERT, UPDATE, or DELETE operations on a table or view.

• AFTER Triggers: Execute after the associated DML operation is completed.
• INSTEAD OF Triggers: Execute in place of the associated DML operation.

DDL Triggers

These fire in response to data definition language (DDL) events such as CREATE, ALTER, or DROP statements.

Real-Time Use Case Example

Scenario. Imagine a retail company that wants to maintain an audit trail for all changes made to the Orders table. Every time an order is inserted, updated, or deleted, the company wants to log the details of the operation in an OrderAudit table.

Setting Up the Example

First, let’s create the Orders table and the OrderAudit table.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATETIME,
    Amount DECIMAL(10, 2)
);

CREATE TABLE OrderAudit (
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    OrderID INT,
    Operation VARCHAR(10),
    OldCustomerID INT,
    NewCustomerID INT,
    OldOrderDate DATETIME,
    NewOrderDate DATETIME,
    OldAmount DECIMAL(10, 2),
    NewAmount DECIMAL(10, 2),
    ChangeDate DATETIME DEFAULT GETDATE()
);

Creating Triggers

AFTER INSERT Trigger

CREATE TRIGGER trg_AfterInsert_Orders
ON Orders
AFTER INSERT
AS
BEGIN
    INSERT INTO OrderAudit (OrderID, Operation, NewCustomerID, NewOrderDate, NewAmount)
    SELECT OrderID, 'INSERT', CustomerID, OrderDate, Amount
    FROM inserted;
END;


-- Insert a new order
INSERT INTO Orders (OrderID, CustomerID, OrderDate, Amount)
VALUES (1, 101, '2024-01-01', 100.00);

-- Check audit logs
select * from  OrderAudit

Result

We can see we have a record inserted into the audit log table, and when any record is inserted into the order table, we will know what operation was performed and at what time it was executed.

AFTER INSERT Trigger: When a new order is inserted into the Orders table, the trg_AfterInsert_Orders trigger fires. It captures the new order details from the inserted pseudo-table and inserts a corresponding record into the OrderAudit table with the operation type 'INSERT'.

2. AFTER UPDATE Trigger

CREATE TRIGGER trg_AfterUpdate_Orders
ON Orders
AFTER UPDATE
AS
BEGIN
    INSERT INTO OrderAudit (OrderID, Operation, OldCustomerID, NewCustomerID, OldOrderDate, NewOrderDate, OldAmount, NewAmount)
    SELECT 
        i.OrderID, 
        'UPDATE', 
        d.CustomerID, 
        i.CustomerID, 
        d.OrderDate, 
        i.OrderDate, 
        d.Amount, 
        i.Amount
    FROM inserted i
    INNER JOIN deleted d ON i.OrderID = d.OrderID;
END;

-- Update the order
UPDATE Orders
SET Amount = 120.00
WHERE OrderID = 1;

-- Audit log
SELECT * FROM OrderAudit;

Result

we can see at this time, we updated the record, and we can see details of updated records, this is very helpful in case when we want to track how many time a order has been updated etc.

AFTER UPDATE Trigger: When an existing order is updated, the trg_AfterUpdate_Orders trigger fires. It captures both the old and new values by joining the inserted and deleted pseudo-tables and inserts a record into the OrderAudit table with the operation type 'UPDATE'.

3. AFTER DELETE Trigger

CREATE TRIGGER trg_AfterDelete_Orders
ON Orders
AFTER DELETE
AS
BEGIN
    INSERT INTO OrderAudit (OrderID, Operation, OldCustomerID, OldOrderDate, OldAmount)
    SELECT 
        OrderID, 
        'DELETE', 
        CustomerID, 
        OrderDate, 
        Amount
    FROM deleted;
END;

-- Delete the order
DELETE FROM Orders
WHERE OrderID = 1;

-- Reading audit table
SELECT * FROM OrderAudit;

Result. We can see that order data has been deleted, and we can check logs to see what time it was deleted.

AFTER DELETE Trigger: When an order is deleted, the trg_AfterDelete_Orders trigger fires. It captures the details of the deleted order from the deleted pseudo-table and inserts a corresponding record into the OrderAudit table with the operation type 'DELETE'.

Dropping a Trigger

Just use DROP triggername.

Instead of Trigger

Let’s consider a table of Employees where we want to restrict the insertion of records such that the Salary must be above a certain threshold (e.g., $30,000). If the Salary is below this threshold, the insertion should be rejected.

We can use it instead of Trigger in this case.

CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(100),
    Position VARCHAR(50),
    Salary DECIMAL(10, 2)
);


CREATE TRIGGER trg_InsteadOfInsert_Employees
ON Employees
INSTEAD OF INSERT
AS
BEGIN
    IF EXISTS (SELECT * FROM inserted WHERE Salary < 30000)
    BEGIN
        -- Raise an error and roll back the transaction if the salary is below the threshold
        RAISERROR ('Salary must be at least $30,000', 16, 1);
        ROLLBACK TRANSACTION;
    END
    ELSE
    BEGIN
        -- Perform the actual insert if the condition is met
        INSERT INTO Employees (Name, Position, Salary)
        SELECT Name, Position, Salary
        FROM inserted;
    END
END;


--

--less than 90000
-- This insertion should  fail
INSERT INTO Employees (Name, Position, Salary)
VALUES ('devesh omar', 'Developer', 89999);

Explanation

1. The trigger intercepts the insert operation and checks if any of the inserted rows have a Salary below $90,000.
2. If any rows do not meet the condition, an error is raised, and the transaction is rolled back, preventing the insertion.
3. If all rows meet the condition, the insertion proceeds by inserting the rows into the Employees table.

Thanks

We have learned triggers.