How to Implement Database Triggers in SQL Server with Example

Introduction

If you are learning SQL Server or working as a backend developer in India (Noida, Ghaziabad, Delhi NCR, Bengaluru), you will often hear about database triggers. Triggers are very useful in real-world applications where you want certain actions to happen automatically when data changes.

For example, whenever a new record is inserted, you may want to log it, validate it, or update another table automatically. This is where SQL Server triggers come into play.

In this detailed guide, you will learn how to implement database triggers in SQL Server step by step, using simple language, real-world examples, and practical explanations.

What is a Trigger in SQL Server?

A trigger in SQL Server is a special type of stored procedure that automatically runs when a specific event occurs in a database table.

In simple words:

It runs automatically
It is attached to a table
It reacts to events like INSERT, UPDATE, DELETE

Simple Example

Imagine you run an e-commerce website:

When a new order is placed → save it
Automatically log the order in another table

This automatic action is handled by a trigger.

Why Use Triggers in SQL Server?

Triggers are widely used in real-world database applications.

Common Use Cases

Audit logging (track changes)
Data validation
Enforcing business rules
Synchronizing tables

Benefits

Automation
Data consistency
Reduced manual work

Limitation

Can slow down performance if overused

Types of Triggers in SQL Server

There are mainly two types of triggers used in SQL Server.

1. AFTER Trigger

This trigger runs after the operation is completed.

Used for:

Logging
Auditing

2. INSTEAD OF Trigger

This trigger runs instead of the actual operation.

Used for:

Custom validation
Preventing invalid operations

Events That Can Fire a Trigger

Triggers are executed when these events happen:

INSERT
UPDATE
DELETE

These are called DML (Data Manipulation Language) events.

Step-by-Step: How to Create a Trigger in SQL Server

Let’s go step by step with a real example.

Step 1: Create a Sample Table

CREATE TABLE Employees (
    Id INT PRIMARY KEY,
    Name VARCHAR(50),
    Salary INT
);

Step 2: Create an Audit Table

CREATE TABLE Employee_Audit (
    AuditId INT IDENTITY(1,1),
    EmpId INT,
    ActionType VARCHAR(10),
    ActionDate DATETIME
);

This table will store logs.

Step 3: Create an AFTER INSERT Trigger

CREATE TRIGGER trg_AfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
    INSERT INTO Employee_Audit (EmpId, ActionType, ActionDate)
    SELECT Id, 'INSERT', GETDATE()
    FROM inserted;
END;

Explanation in Simple Words

"inserted" is a special table
It stores new records
Trigger copies data into audit table

Step 4: Test the Trigger

INSERT INTO Employees VALUES (1, 'Rahul', 50000);

Now check:

SELECT * FROM Employee_Audit;

You will see the log automatically created.

Understanding inserted and deleted Tables

SQL Server provides two special tables inside triggers.

inserted Table

Contains new data
Used in INSERT and UPDATE

deleted Table

Contains old data
Used in DELETE and UPDATE

Example: UPDATE Trigger

CREATE TRIGGER trg_AfterUpdate
ON Employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO Employee_Audit (EmpId, ActionType, ActionDate)
    SELECT Id, 'UPDATE', GETDATE()
    FROM inserted;
END;

Example: DELETE Trigger

CREATE TRIGGER trg_AfterDelete
ON Employees
AFTER DELETE
AS
BEGIN
    INSERT INTO Employee_Audit (EmpId, ActionType, ActionDate)
    SELECT Id, 'DELETE', GETDATE()
    FROM deleted;
END;

Real-Life Use Case

Imagine a banking system:

When money is withdrawn
Automatically log transaction

Without triggers:

Developer must write extra code

With triggers:

Everything happens automatically

Best Practices for Using Triggers

Keep triggers simple
Avoid heavy logic
Test performance
Use only when necessary

Advantages of Triggers

Automatic execution
Data consistency
Useful for auditing

Disadvantages of Triggers

Hard to debug
Can reduce performance
Hidden logic (not visible easily)

Common Mistakes to Avoid

Writing complex logic inside triggers
Not handling multiple rows
Ignoring performance impact

Summary

Database triggers in SQL Server are automatic procedures that run when data changes occur. By using triggers, developers can implement logging, validation, and business rules efficiently. With proper implementation and best practices, triggers can greatly enhance database functionality and reliability in real-world applications.