SQL Server  

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.

Membership not found