SQL Server  

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:

  1. A queue table to store notifications

  2. Triggers to detect changes

  3. A polling API or SQL Job to process notifications

  4. 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

Membership not found