Skip to content
Tutorials Center
My learning

Topics

Lesson 37 of 39 ยท Projects and Practice

Project: Inventory with an Audit Trail

Build an inventory system with stock movements, a derived stock level, low-stock alerts and an automatic audit trail, with T-SQL triggers and runnable portable versions.

Warehouses live on one rule: never overwrite the truth, record what happened. Instead of a single Quantity that gets updated until nobody knows why it changed, you store every movement (received, shipped, adjusted) and calculate the stock from them. Then you add an audit trail that records who changed product data and when.

In this project you build both pieces: a movement ledger with derived stock, and an audit trigger. You apply triggers, views and set-based queries, constraints and MERGE-style upserts. T-SQL code is read-only; each step has a runnable portable twin.

The model

erDiagram
  PRODUCTS ||--o{ STOCK_MOVEMENTS : "has"
  PRODUCTS ||--o{ PRODUCT_AUDIT : "is audited in"
  PRODUCTS {
    int ProductId
    string Sku
    int ReorderLevel
  }
  STOCK_MOVEMENTS {
    int MovementId
    int Delta
    string Reason
  }

Stock is not stored: it is the sum of the movements.

SQL
CREATE TABLE dbo.Products (
    ProductId    int IDENTITY PRIMARY KEY,
    Sku          varchar(20) NOT NULL UNIQUE,
    Name         nvarchar(100) NOT NULL,
    Price        decimal(10,2) NOT NULL CHECK (Price >= 0),
    ReorderLevel int NOT NULL DEFAULT 10
);

CREATE TABLE dbo.StockMovements (
    MovementId int IDENTITY PRIMARY KEY,
    ProductId  int NOT NULL REFERENCES dbo.Products (ProductId),
    Delta      int NOT NULL CHECK (Delta <> 0),          -- positive = in, negative = out
    Reason     varchar(20) NOT NULL CHECK (Reason IN ('received','shipped','returned','adjustment','damaged')),
    MovedAt    datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME(),
    MovedBy    sysname NOT NULL DEFAULT SUSER_SNAME()
);
CREATE INDEX IX_Movements_Product ON dbo.StockMovements (ProductId) INCLUDE (Delta);

CREATE VIEW dbo.CurrentStock AS
SELECT p.ProductId, p.Sku, p.Name, p.ReorderLevel,
       COALESCE(SUM(m.Delta), 0) AS OnHand
FROM   dbo.Products AS p
LEFT   JOIN dbo.StockMovements AS m ON m.ProductId = p.ProductId
GROUP  BY p.ProductId, p.Sku, p.Name, p.ReorderLevel;

Step 1: movements and current stock

The CHECK (Delta <> 0) rejects meaningless rows; Reason is a controlled vocabulary. The view derives OnHand, and LEFT JOIN keeps products that have never moved at zero.

SQL Ledger of movements and the stock view

Press Run (or Ctrl + Enter) to see the result here.

SPRING shows 0 because it has no movements; GEAR-X shows 1 after receiving 20, shipping 18 and writing off 1.

Step 2: low-stock alerts

Products at or below their reorder level need ordering:

SQL What needs reordering?

Press Run (or Ctrl + Enter) to see the result here.

In production you'd schedule this as a SQL Agent job that emails the buyer with sp_send_dbmail.

Step 3: never go negative

A shipment mustn't take more than is on hand. A table CHECK can't see other rows, so use a trigger. In SQL Server an AFTER INSERT trigger works on the whole batch of inserted rows:

SQL
CREATE OR ALTER TRIGGER dbo.trg_Movements_NoNegativeStock
ON dbo.StockMovements
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (
        SELECT 1
        FROM   dbo.StockMovements AS m
        WHERE  m.ProductId IN (SELECT ProductId FROM inserted)
        GROUP  BY m.ProductId
        HAVING SUM(m.Delta) < 0
    )
    BEGIN
        ROLLBACK TRANSACTION;
        THROW 50002, 'Stock cannot go below zero', 1;
    END;
END;

Notice the set-based check on all inserted rows. The SQLite version uses RAISE(ABORT, ...) per row:

SQL A trigger that refuses negative stock

Press Run (or Ctrl + Enter) to see the result here.

Now try shipping 20 from a stock of 6. The statement is aborted and the output shows the error:

SQL The trigger blocks an oversized shipment

Press Run (or Ctrl + Enter) to see the result here.

Under heavy concurrency this check has the same race as any read-then-write. For a hot product, take a lock first (UPDLOCK, HOLDLOCK on the product row) or keep a materialized OnHand column updated in the same transaction.

Step 4: the audit trail

Who changed a price, and what was it before? An AFTER UPDATE trigger writes the old and new values:

SQL
CREATE TABLE dbo.ProductAudit (
    AuditId   int IDENTITY PRIMARY KEY,
    ProductId int NOT NULL,
    Field     varchar(30) NOT NULL,
    OldValue  nvarchar(100) NULL,
    NewValue  nvarchar(100) NULL,
    ChangedBy sysname NOT NULL DEFAULT SUSER_SNAME(),
    ChangedAt datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE OR ALTER TRIGGER dbo.trg_Products_Audit
ON dbo.Products
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.ProductAudit (ProductId, Field, OldValue, NewValue)
    SELECT i.ProductId, 'Price', CONVERT(nvarchar(100), d.Price), CONVERT(nvarchar(100), i.Price)
    FROM   inserted AS i JOIN deleted AS d ON d.ProductId = i.ProductId
    WHERE  i.Price <> d.Price
    UNION ALL
    SELECT i.ProductId, 'Name', d.Name, i.Name
    FROM   inserted AS i JOIN deleted AS d ON d.ProductId = i.ProductId
    WHERE  i.Name <> d.Name;
END;

(Alternatively, make Products a system-versioned temporal table and get the full history without a trigger.) Try the idea:

SQL Audit trail for price and name changes

Press Run (or Ctrl + Enter) to see the result here.

Step 5: report and harden

  • Movement history per product with running balance is a window function: SUM(Delta) OVER (PARTITION BY ProductId ORDER BY MovedAt, MovementId).
  • Deny UPDATE and DELETE on StockMovements for everyone: a ledger is append-only. Corrections are new rows with Reason = 'adjustment'.
  • Grant the warehouse app INSERT on StockMovements and SELECT on CurrentStock only (permissions).
  • Index (ProductId) INCLUDE (Delta) keeps the stock view cheap; for millions of rows, keep a summary table updated by the same transaction.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why store stock movements instead of one Quantity column?

    A ledger preserves history; a single overwritten number loses the reasons and timing of changes.
  2. 2. Why must the T-SQL stock trigger check all rows in inserted rather than one value?

    Statement-level triggers see every affected row in the inserted table.
  3. 3. Which design keeps an audit trail trustworthy?

    Append-only permissions prevent history from being rewritten.
  4. 4. What can replace a hand-written history trigger for a table in SQL Server?

    Temporal tables automatically keep every previous row version with validity periods.

Summary

  • Model events (movements), derive state (stock) and keep history (audit).
  • Constraints guard simple rules; triggers guard cross-row rules but must be set-based.
  • Append-only permissions and temporal tables make the trail trustworthy.

Next: SQL Server practice exercises