Views, Triggers and Sequences — Production Patterns and EF Core Integration

Putting views, triggers, and sequences together creates a layered database architecture where the application interacts with stable, well-defined surfaces (views and stored procedures) while the underlying tables are protected from accidental direct modification. The BlogApp’s production database should expose views for reads, use triggers for audit and counter maintenance, and offer stored procedures for writes — giving the DBA control over the data layer independent of application deployments.

Production Integration Patterns #

-- ── Pattern 1: Row-version optimistic concurrency with trigger ────────────
-- Add rowversion column to Posts for optimistic concurrency:
ALTER TABLE dbo.Posts ADD RowVersion ROWVERSION NOT NULL;
-- ROWVERSION automatically updates on every row modification — no trigger needed
-- EF Core maps this to a concurrency token:
-- .Property(p => p.RowVersion).IsRowVersion();

-- ── Pattern 2: Sequence for batch post import ─────────────────────────────
CREATE SEQUENCE dbo.seq_ImportBatchId AS BIGINT START WITH 1 INCREMENT BY 1;
GO

CREATE OR ALTER PROCEDURE dbo.usp_ImportPosts
    @ImportData NVARCHAR(MAX)   -- JSON array of posts
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @BatchId BIGINT = NEXT VALUE FOR dbo.seq_ImportBatchId;

    BEGIN TRANSACTION;

    INSERT INTO dbo.Posts (AuthorId, Title, Slug, Body, Status, CreatedAt)
    SELECT
        j.AuthorId, j.Title, j.Slug, j.Body,
        'draft',
        SYSUTCDATETIME()
    FROM OPENJSON(@ImportData)
    WITH (
        AuthorId NVARCHAR(450) '$.authorId',
        Title    NVARCHAR(200) '$.title',
        Slug     VARCHAR(200)  '$.slug',
        Body     NVARCHAR(MAX) '$.body'
    ) j;

    COMMIT TRANSACTION;

    SELECT @BatchId AS BatchId, @@ROWCOUNT AS PostsImported;
END;
GO

-- ── Pattern 3: Cascading soft delete with INSTEAD OF trigger ──────────────
CREATE OR ALTER TRIGGER trg_Posts_CascadeSoftDelete
ON dbo.Posts
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRANSACTION;

    -- Soft delete the posts
    UPDATE dbo.Posts
    SET    Status = 'archived', IsPublished = 0, UpdatedAt = SYSUTCDATETIME()
    FROM   dbo.Posts p JOIN deleted d ON d.Id = p.Id;

    -- Cascade to comments
    UPDATE dbo.Comments
    SET    IsApproved = 0
    WHERE  PostId IN (SELECT Id FROM deleted);

    -- Log the operation
    INSERT INTO dbo.AuditLog (TableName, RecordId, Action, ChangedAt)
    SELECT 'Posts', Id, 'SOFT_DELETE', SYSUTCDATETIME() FROM deleted;

    COMMIT TRANSACTION;
END;
GO

EF Core Integration Summary #

// ── Keyless view entity ───────────────────────────────────────────────────
modelBuilder.Entity<PublishedPostSummary>()
    .HasNoKey()
    .ToView("vw_PublishedPostSummary");

// ── Rowversion concurrency token ──────────────────────────────────────────
modelBuilder.Entity<Post>()
    .Property(p => p.RowVersion)
    .IsRowVersion();        // EF Core sends WHERE RowVersion = @orig on UPDATE

// ── Soft delete — override SaveChangesAsync to intercept Remove() ─────────
public override async Task<int> SaveChangesAsync(CancellationToken ct = default)
{
    // Convert state from Deleted to Modified with Status = archived
    foreach (var entry in ChangeTracker.Entries<Post>()
        .Where(e => e.State == EntityState.Deleted))
    {
        entry.State = EntityState.Modified;
        entry.Entity.Status     = "archived";
        entry.Entity.IsPublished = false;
        entry.Entity.UpdatedAt  = DateTime.UtcNow;
    }
    return await base.SaveChangesAsync(ct);
}
// Note: if using the INSTEAD OF DELETE trigger AND this override, one will win.
// Choose ONE approach: either the trigger OR the application override — not both.

Trade-Offs Summary #

Common Mistakes #

Mistake 1 — Undocumented triggers (developers confused by unexpected behaviour) #

❌ Wrong — INSTEAD OF DELETE trigger with no documentation; developer debugs for hours why DELETEs don’t remove rows.

✅ Correct — document all triggers in code comments and a project-level DatabaseTriggers.md file.

Mistake 2 — Both application-level and trigger-level soft delete (double processing) #

❌ Wrong — EF Core SaveChanges override AND INSTEAD OF DELETE trigger both implement soft delete; conflicting behaviour.

✅ Correct — choose one approach per rule; triggers for multi-system databases; application code for single-system.