Creating a SQL-Based Notification System (Trigger + Queue Table)

Building a notification system directly inside SQL Server is a powerful way to track important data events without depending only on application code.
This article explains how to design a trigger-driven notification system using a queue table, with practical scripts, architecture, and implementation steps.

Overview

Many applications need to notify users when something changes in the database:

A new order is created
A stockline quantity changes
A record is updated or deleted
A threshold is crossed

Instead of writing custom logic in every API, we can centralise this inside SQL using:

A queue table to store notifications
Triggers to detect changes
A polling API or SQL Job to process notifications
A frontend UI to display alerts

Core Workflow (Smaller Diagram Style)

Notification Workflow

[User Action/API] 
        ↓
[Table Change] 
        ↓
[Trigger Executes] 
        ↓
[Queue Table Inserts Notification] 
        ↓
[API Polls or Job Reads Queue] 
        ↓
[Frontend Receives Notification]

1. Designing the Notification Queue Table

CREATE TABLE NotificationQueue (
    NotificationId BIGINT IDENTITY(1,1) PRIMARY KEY,
    EntityName VARCHAR(100),
    EntityId BIGINT,
    ActionType VARCHAR(20),    -- Insert, Update, Delete
    OldData NVARCHAR(MAX) NULL,
    NewData NVARCHAR(MAX) NULL,
    CreatedDate DATETIME DEFAULT GETDATE(),
    IsProcessed BIT DEFAULT 0
);

Why this structure?

Stores who/what changed
Holds before and after data
Supports unprocessed vs processed state
Enables dashboards, API, or background jobs to read unread notifications

2. Creating a Trigger to Capture Changes

Example table: StockLine

Insert Trigger

CREATE TRIGGER trg_StockLine_Insert
ON StockLine
AFTER INSERT
AS
BEGIN
    INSERT INTO NotificationQueue (EntityName, EntityId, ActionType, NewData)
    SELECT
        'StockLine',
        i.StockLineId,
        'Insert',
        (SELECT * FROM INSERTED i FOR JSON AUTO)
    FROM INSERTED i;
END

Update Trigger

CREATE TRIGGER trg_StockLine_Update
ON StockLine
AFTER UPDATE
AS
BEGIN
    INSERT INTO NotificationQueue (EntityName, EntityId, ActionType, OldData, NewData)
    SELECT
        'StockLine',
        i.StockLineId,
        'Update',
        (SELECT * FROM DELETED d FOR JSON AUTO),
        (SELECT * FROM INSERTED i FOR JSON AUTO)
    FROM INSERTED i;
END

Delete Trigger

CREATE TRIGGER trg_StockLine_Delete
ON StockLine
AFTER DELETE
AS
BEGIN
    INSERT INTO NotificationQueue (EntityName, EntityId, ActionType, OldData)
    SELECT
        'StockLine',
        d.StockLineId,
        'Delete',
        (SELECT * FROM DELETED d FOR JSON AUTO)
    FROM DELETED d;
END

3. API Endpoint to Fetch Pending Notifications

Controller Action (ASP.NET Core)

[HttpGet("pending")]
public async Task<IActionResult> GetPending()
{
    var data = await _db.NotificationQueue
        .Where(x => x.IsProcessed == false)
        .OrderBy(x => x.CreatedDate)
        .ToListAsync();

    return Ok(data);
}

API to Mark Processed

[HttpPost("mark-processed/{id}")]
public async Task<IActionResult> MarkProcessed(long id)
{
    var item = await _db.NotificationQueue.FindAsync(id);
    if(item == null) return NotFound();

    item.IsProcessed = true;
    await _db.SaveChangesAsync();

    return Ok();
}

4. SQL Job (Optional) to Process Queue Automatically

UPDATE NotificationQueue
SET IsProcessed = 1
WHERE IsProcessed = 0;

You can schedule this job every minute or 30 seconds.

5. Angular UI Example (Simple Notification Panel)

Service

getNotifications() {
  return this.http.get('/api/notifications/pending');
}

Component

ngOnInit() {
  setInterval(() => {
    this.service.getNotifications().subscribe(res => {
      this.notifications = res;
    });
  }, 5000);
}

6. Architecture Diagram (Compact Style)

+--------------------+
|   Application UI   |
+---------+----------+
          |
          ↓
+------------------------+
|  Notification API      |
|  (Read/Mark Processed) |
+-----------+------------+
            |
            ↓
+------------------------+
|   NotificationQueue    |
| (Queue Table in SQL)   |
+-----------+------------+
            |
            ↓
+------------------------+
|   SQL Triggers         |
| (Insert/Update/Delete) |
+------------------------+

7. Sequence Diagram (Small Header)

User → API: Update Record
API → DB: UPDATE StockLine
DB → Trigger: Execute
Trigger → Queue Table: Insert Notification
UI → API: Fetch Pending Notifications
API → UI: Return Notifications

8. Use Cases

Multi-tenant systems

Track tenant-specific activity without logging overhead.

Audit trails

Before/after snapshots stored automatically.

Async workflows

Trigger downstream processes such as SMS, emails, or reports.

Error monitoring

Capture suspicious updates or deletions.

9. Performance Considerations

Keep triggers lightweight
Avoid heavy logic inside triggers
Monitor queue table growth
Archive old notifications periodically

10. Enhancements for Production

Add userId (who changed the record)
Add notification categories
Encryption for sensitive data
Dashboard filters (date range, status, entity)
Retry and dead-letter queues