Stored Procedures, Constraints & Triggers – Các Trường Hợp Sử Dụng Thực Tế Trong Lộ Trình .NET

Một stored procedure không tự động nhanh hơn LINQ, và một trigger không phải cách mặc định để ghi audit. Cả hai là quyết định kiến trúc, đánh đổi bằng khả năng quan sát, versioning và testing. Bài này vạch ranh giới đó trên SQL Server 2022/Azure SQL và PostgreSQL 17/18, kèm cách EF Core 10 gọi xuống database mà không phá change tracker hay migration.

Cập nhật 2026

  • EF Core 10 (10.0.14, LTS) có ExecuteSql/ExecuteSqlAsync cho lệnh không trả dữ liệu, FromSql cho query, và ExecuteUpdateAsync cho cập nhật set-based không qua change tracker.
  • .NET 8 và .NET 9 hết hỗ trợ ngày 2026-11-10. Nếu solution còn chạy .NET 9, việc nâng lên .NET 10 LTS là của tuần này, không phải quý sau.
  • PostgreSQL 17/18 vẫn là lựa chọn mặc định khi bạn cần jsonb, GIN index hoặc deferrable constraints; SQL Server không có deferred constraint.

Constraint: bất biến ở nơi mọi writer phải đi qua

Validation trong C# là trải nghiệm người dùng; constraint là tính đúng đắn. Khác biệt lộ ra khi có writer thứ hai: job import Excel, worker chạy nền, script của DBA, hoặc một service khác dùng chung database. Tất cả đều bỏ qua FluentValidation của bạn.

Bốn constraint đủ cho phần lớn schema giao dịch:

  • PRIMARY KEY — định danh dòng, đồng thời là target của FK và là clustered key mặc định trên SQL Server. Chọn khóa hẹp: int/bigint identity, hoặc Guid nếu cần sinh ID phía client.
  • FOREIGN KEY — chặn tham chiếu tới dòng không tồn tại. Cả SQL Server lẫn PostgreSQL đều không tự tạo index cho cột FK; thiếu index thì join và DELETE trên bảng cha đều chậm.
  • UNIQUE — tính duy nhất ngoài PK, nhận composite. Đây là thứ bảo vệ email, SKU, cặp (TenantId, Slug) khi hai request đồng thời cùng kiểm tra “chưa tồn tại”.
  • CHECK — miền giá trị hoặc bất biến một dòng: Price >= 0, Quantity > 0, trạng thái thuộc tập cho phép.

Schema tối thiểu, viết cho SQL Server:

CREATE TABLE dbo.Products (
    ProductId   int IDENTITY PRIMARY KEY,
    Sku         varchar(32)   NOT NULL,
    Price       decimal(18,2) NOT NULL,
    Status      varchar(16)   NOT NULL,
    CategoryId  int           NOT NULL REFERENCES dbo.Categories(CategoryId),
    CreatedAt   datetime2(0)  NOT NULL CONSTRAINT DF_Products_Created DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_Products_Sku   UNIQUE (Sku),
    CONSTRAINT CK_Products_Price CHECK (Price >= 0),
    CONSTRAINT CK_Products_Status CHECK (Status IN ('Draft','Active','Retired'))
);

Trên PostgreSQL, đổi IDENTITY thành GENERATED ALWAYS AS IDENTITY và datetime2 thành timestamptz; CHECK viết trong cùng CREATE TABLE. Khác biệt đáng nhớ: PostgreSQL cho phép DEFERRABLE INITIALLY DEFERRED trên FK và unique, tức kiểm tra ở thời điểm commit, nên bạn có thể sắp xếp lại thứ tự dòng trong một transaction. SQL Server không có cơ chế này; thứ tự thao tác phải đúng ngay từ câu lệnh đầu.

EF Core map được các constraint này mà không cần SQL tay:

// File: Data/Configurations/ProductConfiguration.cs
public sealed class ProductConfiguration : IEntityTypeConfiguration<Product>
{
    public void Configure(EntityTypeBuilder<Product> builder)
    {
        builder.ToTable("Products", t =>
        {
            t.HasCheckConstraint("CK_Products_Price", "[Price] >= 0");
            t.HasCheckConstraint("CK_Products_Status",
                "[Status] IN ('Draft','Active','Retired')");
        });

        builder.Property(p => p.Sku).HasMaxLength(32).IsRequired();
        builder.Property(p => p.Price).HasPrecision(18, 2);
        builder.Property(p => p.Status).HasConversion<string>().HasMaxLength(16);
        builder.HasIndex(p => p.Sku).IsUnique();
        builder.HasOne(p => p.Category)
               .WithMany(c => c.Products)
               .HasForeignKey(p => p.CategoryId)
               .OnDelete(DeleteBehavior.Restrict);
    }
}

Từ EF Core 7, HasCheckConstraint nằm trên table builder như trên, không còn trên entity builder. Nội dung check viết bằng SQL dialect của provider — model chạy cả SQL Server và PostgreSQL cần hai cấu hình hoặc một nhánh theo provider.

Khi constraint bị vi phạm, đừng bắt theo message. SQL Server trả SqlException.Number: 2627/2601 cho unique, 547 cho FK và check. PostgreSQL trả PostgresException.SqlState: 23505 unique, 23503 FK, 23514 check. Map sang lỗi nghiệp vụ ở một chỗ:

try
{
    await db.SaveChangesAsync(ct);
}
catch (DbUpdateException ex) when (ex.InnerException is SqlException { Number: 2627 or 2601 })
{
    return TypedResults.Conflict(new { error = "SKU đã tồn tại" });
}

Điều constraint không làm được: bất biến liên quan nhiều dòng hoặc nhiều bảng — tồn kho không âm sau khi trừ reservation, tổng dòng chi tiết bằng tổng đơn hàng. Đó là phạm vi của transaction với isolation phù hợp hoặc trigger. Đừng nhồi những quy tắc này vào CHECK; nó chỉ thấy một dòng.

Stored procedure: khi nào thực sự đúng

Lợi ích “biên dịch trước nên nhanh hơn” là lý do sai phổ biến nhất. SQL Server cache plan cho mọi batch, không riêng procedure; PostgreSQL lưu plan theo statement. Procedure nhanh hơn khi nó giảm round trip, giảm dữ liệu truyền, hoặc gộp nhiều bước nguyên tử — không phải vì bản thân nó là procedure.

Bốn trường hợp thực sự đúng:

  • Set-based bulk work trong một round trip. Cập nhật hai triệu dòng có join, merge từ staging table, hoặc tổng hợp nhiều bước với temp table. Client loop hai triệu lần là hai triệu round trip; một câu UPDATE là một.
  • Ranh giới quyền. Cấp EXECUTE trên procedure mà không cấp SELECT trên bảng. SQL Server dùng ownership chaining để procedure chạy với quyền của owner; dynamic SQL bên trong phá chuỗi này trừ khi bạn ký procedure bằng certificate.
  • Schema bạn không sở hữu. Vendor yêu cầu gọi procedure, hoặc nhiều ứng dụng dùng chung một database và procedure là hợp đồng chung.
  • Truy vấn bệnh lý cần can thiệp plan. SQL Server có hint và OPTION (RECOMPILE); PostgreSQL không có hint native, chỉ có extension như pg_hint_plan.

Và đây là lúc nó thành nợ.

  • Logic nằm ngoài code review và ngoài pipeline test. Không unit test, không refactor, không đổi tên an toàn. Mọi thay đổi là một lần deploy database riêng.
  • Hai nguồn sự thật: entity trong C# và procedure trong DB. SQL Server có deferred name resolution — ALTER PROCEDURE vẫn thành công khi bảng hoặc cột chưa tồn tại; lỗi chỉ nổ ở lần chạy đầu tiên.
  • Parameter sniffing: plan cache xây cho tham số không đại diện. SQL Server cần OPTION (RECOMPILE) hoặc OPTIMIZE FOR; PostgreSQL có plan_cache_mode. Bạn đang debug optimizer thay vì debug nghiệp vụ.
  • Không đi qua DI, logging, metrics, transaction boundary của ứng dụng. Observability dừng ở cửa database.

Ví dụ set-based thật, viết cho SQL Server:

CREATE OR ALTER PROCEDURE dbo.usp_ApplyPriceIncrease
    @CategoryId int,
    @Percent    decimal(5,2)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE p
    SET p.Price = p.Price * (1 + @Percent / 100.0)
    FROM dbo.Products AS p
    JOIN dbo.Categories AS c ON c.CategoryId = p.CategoryId
    WHERE c.CategoryId = @CategoryId
      AND p.Status = 'Active';

    SELECT @@ROWCOUNT AS Value;
END;

Cùng logic trên PostgreSQL, dùng function trả về số dòng thay vì OUTPUT parameter:

CREATE OR REPLACE FUNCTION apply_price_increase(p_category_id int, p_percent numeric)
RETURNS int
LANGUAGE plpgsql
AS $$
DECLARE
    v_applied int;
BEGIN
    UPDATE products p
    SET price = p.price * (1 + p_percent / 100)
    FROM categories c
    WHERE c.category_id = p_category_id
      AND p.category_id = c.category_id
      AND p.status = 'Active';

    GET DIAGNOSTICS v_applied = ROW_COUNT;
    RETURN v_applied;
END;
$$;

Gọi từ EF Core 10. Với procedure trả về một cột số, đặt tên cột là Value để SqlQuery<int> đọc được:

var applied = (await db.Database
        .SqlQuery<int>($"EXEC dbo.usp_ApplyPriceIncrease @CategoryId = {categoryId}, @Percent = {percent}")
        .ToListAsync(ct))
    .Single();

Với procedure trả về tập entity, dùng FromSql trên chính DbSet:

var products = await db.Products
    .FromSql($"EXEC dbo.usp_GetProductsByCategory @CategoryId = {categoryId}")
    .AsNoTracking()
    .ToListAsync(ct);

Hai giới hạn phải nhớ. FromSql chỉ dùng trực tiếp trên DbSet, và trên SQL Server không thể compose LINQ lên EXEC — thêm .Where(...) sẽ sinh SQL không hợp lệ. Nếu cần lọc thêm, gọi .AsAsyncEnumerable() ngay sau FromSql rồi xử lý trong bộ nhớ, hoặc đưa điều kiện vào tham số của procedure. Kết quả cũng phải trả đủ mọi cột mà entity map tới.

Khi logic biểu diễn được bằng LINQ, đừng viết procedure

EF Core 10 xử lý được phần lớn cập nhật set-based đơn giản. Cùng nghiệp vụ trên, không cần procedure:

var applied = await db.Products
    .Where(p => p.CategoryId == categoryId && p.Status == ProductStatus.Active)
    .ExecuteUpdateAsync(s => s.SetProperty(
        p => p.Price, p => p.Price * (1 + percent / 100m)), ct);

ExecuteUpdateAsync chạy đúng một câu UPDATE, trả về số dòng bị tác động, không load entity, không qua change tracker. Đổi lại: nó không cập nhật entity đang tracked (nên dữ liệu trong bộ nhớ có thể cũ), trigger database vẫn chạy, và nó không tự động nằm trong transaction với SaveChanges sau đó. Nếu cần cả hai, mở transaction tường minh.

Quy tắc chọn: nếu logic là một câu SQL mà LINQ dịch được, dùng LINQ — nó hiển thị trong code review, có test, có migration. Procedure chỉ có lý khi cần CTE đệ quy, window function phức tạp, temp table, hoặc ranh giới quyền.

Trigger: phương án cuối, không phải mặc định

Trigger đúng khi side effect không được phép bỏ sót và mọi writer đều đi qua database. Hai trường hợp chính đáng: audit trail như lớp phòng thủ cuối, và duy trì dữ liệu denormalized trong cùng transaction.

Ví dụ audit giá, SQL Server:

CREATE OR ALTER TRIGGER dbo.trg_Products_PriceAudit
ON dbo.Products
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT UPDATE(Price) RETURN;

    INSERT INTO dbo.ProductPriceHistory
        (ProductId, OldPrice, NewPrice, ChangedBy, ChangedAt)
    SELECT i.ProductId,
           d.Price,
           i.Price,
           COALESCE(CAST(SESSION_CONTEXT(N'app_user') AS nvarchar(128)), SUSER_SNAME()),
           SYSUTCDATETIME()
    FROM inserted AS i
    JOIN deleted  AS d ON d.ProductId = i.ProductId
    WHERE i.Price <> d.Price;
END;

PostgreSQL dùng row-level trigger với WHEN, và current_setting(..., true) trả NULL thay vì lỗi khi chưa set:

CREATE OR REPLACE FUNCTION audit_product_price()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO product_price_history
        (product_id, old_price, new_price, changed_by, changed_at)
    VALUES (OLD.product_id, OLD.price, NEW.price,
            current_setting('app.current_user', true), now());
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_product_price_audit
AFTER UPDATE OF price ON products
FOR EACH ROW
WHEN (OLD.price IS DISTINCT FROM NEW.price)
EXECUTE FUNCTION audit_product_price();

Bốn bẫy làm trigger thành sự cố:

  • Đệ quy và lồng nhau. SQL Server mặc định tắt recursive trigger (RECURSIVE_TRIGGERS), nhưng nested trigger bật (nested triggers server option) và tối đa 32 mức. Một trigger cập nhật bảng khác có trigger riêng sẽ tạo chuỗi khó thấy. PostgreSQL không giới hạn độ sâu; dùng pg_trigger_depth() > 0 để thoát sớm.
  • Deadlock do thứ tự khóa. Trigger cập nhật bảng phụ trong cùng transaction; hai transaction ngược thứ tự sẽ deadlock. Giữ trigger ngắn, không gọi HTTP, không WAITFOR/pg_sleep, không ghi sang database khác.
  • UPDATE(Price) không có nghĩa “giá đã đổi”. Nó chỉ đúng khi cột Price nằm trong danh sách SET, kể cả gán cùng giá trị. Phải so inserted với deleted, hoặc OLD với NEW như ví dụ trên.
  • Audit không biết ai thay đổi. Trigger chạy dưới quyền database, nên SUSER_SNAME() thường trả tài khoản ứng dụng. Phải set session context trong request: SQL Server sp_set_session_context, PostgreSQL set_config('app.current_user', ..., true). Không set thì cột ChangedBy vô nghĩa.

Còn một chi tiết SQL Server dễ mất buổi chiều: bảng có trigger đang bật thì câu INSERT ... OUTPUT không có INTO sẽ báo lỗi 334. Nếu ETL cần OUTPUT, thêm INTO vào bảng tạm hoặc tắt trigger đúng cách khi bảo trì.

Trigger không thay thế outbox pattern. Cần publish event sang message broker thì ghi outbox trong cùng transaction rồi một worker đọc; trigger gọi thẳng ra ngoài sẽ treo transaction khi broker chậm.

Migration và ba công cụ này

Model snapshot của EF Core chỉ chứa entity, property, index, constraint khai báo trong model. Procedure, trigger, view, function, grant đều không nằm trong đó. Vì vậy:

  • dotnet ef migrations add không bao giờ sinh file cho procedure hay trigger bạn tạo tay.
  • dotnet ef migrations has-pending-model-changes không phát hiện drift của các object này; nó chỉ so model với snapshot.
  • Để version hóa, viết chúng trong migration bằng migrationBuilder.Sql(...), kèm Down thật sự chạy được.
migrationBuilder.Sql("""
    CREATE OR ALTER TRIGGER dbo.trg_Products_PriceAudit
    ON dbo.Products AFTER UPDATE AS ...
    """);

// Down
migrationBuilder.Sql("DROP TRIGGER IF EXISTS dbo.trg_Products_PriceAudit;");

Dùng CREATE OR ALTER trên SQL Server và CREATE OR REPLACE trên PostgreSQL để migration chạy lại trên database mới không lỗi. Grant quyền cũng nên nằm trong migration hoặc trong script provisioning có version, đừng để trong đầu một người.

Bảng quyết định

Nhu cầu Dùng Tránh khi
Bất biến một dòng: giá, số lượng, miền trạng thái CHECK, NOT NULL, UNIQUE Quy tắc cần đọc nhiều dòng
Quan hệ và xóa có kiểm soát FOREIGN KEY + index trên cột FK Bảng log không cần tham chiếu
Cập nhật hàng loạt bằng một câu SQL LINQ + ExecuteUpdateAsync Cần CTE đệ quy, temp table, hint
Bulk work nhiều bước, một round trip Stored procedure Logic còn thay đổi thường xuyên
Ranh giới quyền, schema của vendor Stored procedure Bạn sở hữu cả schema và ứng dụng
Audit không được bỏ sót Trigger + session context Cần publish event, cần test dễ

2027

.NET 11 GA vào tháng 11/2026 và là bản STS; production vẫn nên bám .NET 10 LTS đến 2028. EF Core 11 mở rộng thêm ExecuteUpdate và JSON/vector, nhưng ranh giới trong bài này — constraint để giữ đúng, procedure để gói set-based work, trigger như lớp cuối — không đổi theo version.

Checklist

  • Mọi bảng giao dịch có PK; mọi cột FK có index; mọi cột nghiệp vụ quan trọng có CHECK hoặc UNIQUE tương ứng.
  • Map lỗi 2627/2601/547 và 23505/23503/23514 sang ProblemDetails ở một chỗ duy nhất.
  • Procedure chỉ dùng khi có lý do round trip, quyền hoặc legacy; vẫn phải có test tích hợp.
  • Trigger có guard chống đệ quy, không gọi network, có session context cho audit.
  • Procedure, trigger và grant được tạo trong migration với Up và Down chạy được.

Đọc tiếp: SQL và cơ sở dữ liệu quan hệ, EF Core Code-First, EF Core Migrations, và EF Core, Dapper hay RepoDB.