Trigger in SQL Server with Example

In SQL Server (MSSQL), a trigger is a special type of database object that automatically executes a set of actions in response to specific events or changes that occur in the database. These events can include data modifications (INSERT, UPDATE, DELETE) on tables or specific database operations like creating or altering tables. Triggers are useful for enforcing business rules, auditing changes, maintaining data integrity, and automating certain tasks within the database.

Triggers are implemented using Transact-SQL (T-SQL) and can be classified into two main types based on when they are executed:

1. DML Triggers (Data Manipulation Language Triggers): These triggers are fired in response to data manipulation operations such as INSERT, UPDATE, or DELETE on a table.

2. DDL Triggers (Data Definition Language Triggers): These triggers are fired in response to data definition language events such as creating or altering a table, view, stored procedure, etc.

Now, let's dive into a detailed example of how to create and use a DML trigger in MSSQL:

Suppose we have two tables in our database: "Products" and "ProductAuditLog." The "Products" table stores information about various products, and the "ProductAuditLog" table will be used to track any changes made to the "Products" table.

Creating the Tables: Let's create the "Products" and "ProductAuditLog" tables with some sample data.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
);

CREATE TABLE ProductAuditLog (
    LogID INT PRIMARY KEY,
    ProductID INT,
    OldProductName VARCHAR(100),
    NewProductName VARCHAR(100),
    OldPrice DECIMAL(10, 2),
    NewPrice DECIMAL(10, 2),
    ChangeDate DATETIME
);

Creating the DML Trigger: Now, let's create a trigger named "trg_Products_AuditLog" that will be fired whenever an update operation is performed on the "Products" table. This trigger will capture the old and new values of the updated row and log them in the "ProductAuditLog" table.

CREATE TRIGGER trg_Products_AuditLog
ON Products
AFTER UPDATE
AS
BEGIN
    INSERT INTO ProductAuditLog (ProductID, OldProductName, NewProductName, OldPrice, NewPrice, ChangeDate)
    SELECT 
        d.ProductID,
        d.ProductName AS OldProductName,
        i.ProductName AS NewProductName,
        d.Price AS OldPrice,
        i.Price AS NewPrice,
        GETDATE() AS ChangeDate
    FROM deleted d
    INNER JOIN inserted i ON d.ProductID = i.ProductID;
END;

In this trigger, we use two special tables, "inserted" and "deleted," to access the old and new values of the rows affected by the UPDATE operation. The "inserted" table contains the new values, and the "deleted" table contains the old values.

Testing the Trigger: Let's test the trigger by updating a row in the "Products" table and observe the changes recorded in the "ProductAuditLog" table.

-- Inserting sample data into the Products table
INSERT INTO Products (ProductID, ProductName, Price)
VALUES (1, 'Widget', 10.00), (2, 'Gadget', 20.00);

-- Performing an update operation on the Products table
UPDATE Products
SET ProductName = 'New Widget', Price = 15.00
WHERE ProductID = 1;

Checking the ProductAuditLog table: Now, let's see the content of the "ProductAuditLog" table after the update operation:

SELECT * FROM ProductAuditLog;

The result will show the following row recorded in the "ProductAuditLog" table:

LogID | ProductID | OldProductName | NewProductName | OldPrice | NewPrice | ChangeDate
------+-----------+-----------------+----------------+----------+----------+-----------------------
1     | 1         | Widget          | New Widget     | 10.00    | 15.00    | 2023-07-29 12:34:56

As you can see, the trigger successfully captured the changes made to the "Products" table and logged them in the "ProductAuditLog" table.

Remember that triggers are powerful database objects, but they should be used judiciously to avoid unintended side effects or performance issues. Always test and validate your triggers thoroughly before deploying them in a production environment.