Constraints vs Triggers vs Procedures: Where to Enforce a SQL Server Business Rule

You now have four genuinely different tools capable of enforcing the same underlying rule — CHECK constraints (Fundamentals Ch.6), foreign keys, triggers (next up, Chapter 7), and stored procedures (just built). Choosing the right layer is a decision every schema designer eventually faces, and getting it wrong in either direction — too rigid or too loose — causes real, recurring pain.

The Options, Compared

The Guiding Principle

A single-column rule belongs in a CHECK constraint — cheap, guaranteed, self-documenting, and enforced no matter what wrote the data (the exact reasoning from Fundamentals Chapter 6). A rule spanning multiple tables usually needs a trigger, precisely because CHECK constraints can’t reference other tables. A rule that’s genuinely a business process — multiple steps, conditional branches, needing to notify or log along the way — belongs in a stored procedure, provided your architecture guarantees that procedure is the only path data takes into the table.

Walking a Real Rule Through All Four Layers

Take: “a booking’s total nights can never exceed 30.” Watch how the same rule looks completely different depending on which layer enforces it:

-- As a CHECK constraint: works ONLY if check_in/check_out are both plain columns on one row
ALTER TABLE dbo.Booking ADD CONSTRAINT CK_MaxStay CHECK (DATEDIFF(DAY, check_in, check_out) <= 30);

-- As a trigger: needed instead if the rule required looking at OTHER bookings
-- (e.g. "no guest can have more than 30 total nights booked across all their reservations")
CREATE TRIGGER trg_Booking_MaxTotalNights ON dbo.Booking AFTER INSERT AS
BEGIN
    IF EXISTS (
        SELECT guest_id FROM dbo.Booking
        WHERE guest_id IN (SELECT guest_id FROM inserted)
        GROUP BY guest_id HAVING SUM(DATEDIFF(DAY, check_in, check_out)) > 30
    )
        THROW 51020, 'Guest exceeds 30 total booked nights.', 1;
END;

The single-row version is a CHECK constraint’s job exactly. The moment the rule needs to look across multiple rows (all of one guest’s bookings, not just the one being inserted), a CHECK constraint structurally cannot do it — that’s the trigger’s job instead. This is the actual decision criterion, not a vague sense of “complexity.”