Where a Business Rule Should Actually Live

The business rules database developers enforce should remain true whichever application writes the data. Choose the enforcement point by the guarantee required, not by which team happens to own the first screen.

Start With the Invariant

An invariant is a condition that must remain true after a valid operation. An order identifier must be unique, or a quantity must be nonnegative. State the rule plainly before choosing a technology.

Some rules describe stored data, while others describe a workflow or user experience. A required approval step may involve several systems. Treating every rule as the same kind of problem leads to awkward enforcement.

Ask which entry points can modify the data. Applications, imports, administrative scripts, and scheduled jobs may all write to the same table. A rule enforced only in one screen leaves the other paths open.

Use Constraints for Table-Level Truths

NOT NULL, CHECK, UNIQUE, and FOREIGN KEY constraints express many durable data rules directly. They apply when qualifying writes reach the table. This keeps basic integrity close to the data.

CREATE TABLE #RuleDemo
(
    OrderId int NOT NULL PRIMARY KEY,
    ExternalReference nvarchar(30) NOT NULL UNIQUE,
    Quantity int NOT NULL CHECK (Quantity >= 0),
    StartDate date NOT NULL,
    EndDate date NULL,
    CHECK (EndDate IS NULL OR EndDate >= StartDate)
);
INSERT #RuleDemo VALUES (1, N'ORDER-A', 2, '20260101', NULL);

A CHECK constraint rejects FALSE, while an UNKNOWN result from NULL can pass. Use NOT NULL separately when a value is required. The end-date rule above intentionally allows a missing end date.

BEGIN TRY
    INSERT #RuleDemo VALUES (2, N'ORDER-B', -1, '20260101', NULL);
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS error_number,
           ERROR_MESSAGE() AS error_message;
END CATCH;

The invalid quantity is a deliberate lab test. Keep expected failures in your test cases so later schema changes cannot quietly weaken the rule. Review disabled or untrusted constraints after unusual loading or maintenance operations.

Use Procedures for Controlled Operations

A procedure can coordinate several statements and return a useful business error. It can own a transaction and expose a narrow application interface. That makes it useful for operations such as reserving stock or recording a payment.

Its guarantee depends on callers being required to use the interface. Direct table permissions can bypass procedural checks. Keep fundamental constraints underneath the procedure whenever they express the same invariant safely.

SELECT USER_NAME() AS database_user;
SELECT permission_name
FROM sys.fn_my_permissions(NULL, 'DATABASE')
ORDER BY permission_name;

Review effective access under the application's real identity, including inherited permissions. Also design concurrency explicitly. Checking that something is available and then updating it later can race with another session.

Use Triggers for the Cases That Need Them

A trigger can respond to table changes regardless of which ordinary DML caller initiated them. It can enforce rules that do not fit a simple constraint. It also adds behavior that is less visible in the original statement.

Write triggers for sets of rows, not an assumed single row. The inserted and deleted tables can contain multiple rows from one statement. A scalar assignment that happens to work during a manual test can fail logically during a bulk change.

SELECT OBJECT_SCHEMA_NAME(parent_id) AS schema_name,
       OBJECT_NAME(parent_id) AS table_name,
       name AS trigger_name, is_disabled, is_instead_of_trigger
FROM sys.triggers
WHERE parent_class = 1
ORDER BY schema_name, table_name, trigger_name;

Keep trigger work focused and account for transaction duration and error behavior. Avoid hiding remote calls or lengthy workflows behind a simple table update. Document the trigger alongside the table's write contract.

Use Application Validation for Helpful Feedback

Applications can reject invalid input early and explain the problem in language the user understands. That improves the experience and reduces avoidable database work. It does not replace the final integrity boundary.

Rules involving external approvals or services may belong primarily in application workflow. Persist enough state to enforce allowed transitions and support retries. A database cannot infer an external decision that was never reliably recorded.

Some validation will appear in both places for different reasons. The application provides immediate guidance, while the database protects shared truth. Keep the definitions aligned through shared tests and a named rule owner.

Test Every Way Into the Data

Test normal input, invalid input, multiple-row changes, and competing sessions where relevant. Include imports and administrative paths, not only the main application. Record which layer is responsible for each guarantee.

When a rule changes, review existing rows as well as future writes. New enforcement does not automatically repair old violations. A useful design tells you where the rule lives and how you know it still holds.

A business rule is not protected by its location alone, it is protected by an enforceable boundary.

This post was rewritten from scratch in September 2026. The original, published on 2011-10-11, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.