如何创建Insert/Update触发器校验同订单产品兼容性

需求说明 #

需要在[Order]表上创建Insert和Update触发器，确保同一IDOrder对应的所有产品组件，在Caixa（机箱类型）、Socket（CPU插槽）、TipoRAM（内存类型）三个维度上保持兼容，且不受组件添加顺序的影响。

已知表结构 #

CREATE TABLE Compactibility(
  IDProduct int NOT NULL FOREIGN KEY REFERENCES Produto(IDProduto),
  Caixa nvarchar(50) NOT NULL CHECK (Caixa IN ('ATX', 'Micro-ATX', 'ALL')), 
  Socket nvarchar(50) NOT NULL CHECK (Socket IN ('LGA2066','LGA1700', 'A76M', 'NONE')),
  TipoRAM nvarchar(7) NOT NULL CHECK (TipoRAM IN ('NONE','DDR4','DDR5')),
)
GO

CREATE TABLE [Order](
  IDOrder int NOT NULL PRIMARY KEY identity(1,1),
  IDProduct int FOREIGN KEY REFERENCES Product(IDProduct)
)
GO

注意：Compactibility表引用的是Produto，而[Order]表引用的是Product，请确认这两个表是否为同一对象，避免外键关联错误。

兼容性规则定义 #

明确兼容性判断逻辑：

• Caixa（机箱）：若组件的Caixa值不是ALL，则同一订单内所有非ALL的Caixa必须完全一致；ALL可与任何机箱类型兼容
• Socket（CPU插槽）：若组件的Socket值不是NONE，则同一订单内所有非NONE的Socket必须完全一致；NONE可与任何插槽类型兼容
• TipoRAM（内存类型）：若组件的TipoRAM值不是NONE，则同一订单内所有非NONE的TipoRAM必须完全一致；NONE可与任何内存类型兼容

触发器实现 #

1. Insert触发器 #

插入新订单组件时，校验当前订单下所有组件的兼容性：

CREATE TRIGGER trg_Order_Insert_CheckCompatibility
ON [Order]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @IDOrder int = (SELECT IDOrder FROM inserted);

    -- 校验Caixa兼容性
    IF EXISTS (
        SELECT 1
        FROM [Order] o
        JOIN Compactibility c ON o.IDProduct = c.IDProduct
        WHERE o.IDOrder = @IDOrder
          AND c.Caixa != 'ALL'
        GROUP BY o.IDOrder
        HAVING COUNT(DISTINCT c.Caixa) > 1
    )
    BEGIN
        RAISERROR('同一订单内存在不兼容的机箱类型', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END

    -- 校验Socket兼容性
    IF EXISTS (
        SELECT 1
        FROM [Order] o
        JOIN Compactibility c ON o.IDProduct = c.IDProduct
        WHERE o.IDOrder = @IDOrder
          AND c.Socket != 'NONE'
        GROUP BY o.IDOrder
        HAVING COUNT(DISTINCT c.Socket) > 1
    )
    BEGIN
        RAISERROR('同一订单内存在不兼容的CPU插槽类型', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END

    -- 校验TipoRAM兼容性
    IF EXISTS (
        SELECT 1
        FROM [Order] o
        JOIN Compactibility c ON o.IDProduct = c.IDProduct
        WHERE o.IDOrder = @IDOrder
          AND c.TipoRAM != 'NONE'
        GROUP BY o.IDOrder
        HAVING COUNT(DISTINCT c.TipoRAM) > 1
    )
    BEGIN
        RAISERROR('同一订单内存在不兼容的内存类型', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END
GO

2. Update触发器 #

更新订单组件时，重新校验对应订单的组件兼容性：

CREATE TRIGGER trg_Order_Update_CheckCompatibility
ON [Order]
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF UPDATE(IDOrder) OR UPDATE(IDProduct)
    BEGIN
        DECLARE @AffectedOrders TABLE (IDOrder int);
        INSERT INTO @AffectedOrders
        SELECT IDOrder FROM inserted
        UNION
        SELECT IDOrder FROM deleted;

        DECLARE @IDOrder int;
        DECLARE order_cursor CURSOR FOR SELECT IDOrder FROM @AffectedOrders;
        OPEN order_cursor;
        FETCH NEXT FROM order_cursor INTO @IDOrder;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- 校验Caixa兼容性
            IF EXISTS (
                SELECT 1
                FROM [Order] o
                JOIN Compactibility c ON o.IDProduct = c.IDProduct
                WHERE o.IDOrder = @IDOrder
                  AND c.Caixa != 'ALL'
                GROUP BY o.IDOrder
                HAVING COUNT(DISTINCT c.Caixa) > 1
            )
            BEGIN
                RAISERROR('订单ID %d 内存在不兼容的机箱类型', 16, 1, @IDOrder);
                ROLLBACK TRANSACTION;
                CLOSE order_cursor;
                DEALLOCATE order_cursor;
                RETURN;
            END

            -- 校验Socket兼容性
            IF EXISTS (
                SELECT 1
                FROM [Order] o
                JOIN Compactibility c ON o.IDProduct = c.IDProduct
                WHERE o.IDOrder = @IDOrder
                  AND c.Socket != 'NONE'
                GROUP BY o.IDOrder
                HAVING COUNT(DISTINCT c.Socket) > 1
            )
            BEGIN
                RAISERROR('订单ID %d 内存在不兼容的CPU插槽类型', 16, 1, @IDOrder);
                ROLLBACK TRANSACTION;
                CLOSE order_cursor;
                DEALLOCATE order_cursor;
                RETURN;
            END

            -- 校验TipoRAM兼容性
            IF EXISTS (
                SELECT 1
                FROM [Order] o
                JOIN Compactibility c ON o.IDProduct = c.IDProduct
                WHERE o.IDOrder = @IDOrder
                  AND c.TipoRAM != 'NONE'
                GROUP BY o.IDOrder
                HAVING COUNT(DISTINCT c.TipoRAM) > 1
            )
            BEGIN
                RAISERROR('订单ID %d 内存在不兼容的内存类型', 16, 1, @IDOrder);
                ROLLBACK TRANSACTION;
                CLOSE order_cursor;
                DEALLOCATE order_cursor;
                RETURN;
            END

            FETCH NEXT FROM order_cursor INTO @IDOrder;
        END

        CLOSE order_cursor;
        DEALLOCATE order_cursor;
    END
END
GO

说明 #

• 触发器使用AFTER触发，在记录插入/更新完成后进行校验，若不兼容则回滚事务
• Update触发器考虑了IDOrder或IDProduct修改的情况，会同时校验原订单和新订单的兼容性
• 兼容性校验逻辑通过分组统计不同值的数量判断冲突，确保不受组件添加顺序影响

内容的提问来源于stack exchange，提问作者Antônio Velez