时间戳

首页 > 解决方案 > MSSQL2016 UPDATE 触发器根据位列的 SUM 在其他表中插入记录

sql-server - MSSQL2016 UPDATE 触发器根据位列的 SUM 在其他表中插入记录

问题描述

我正在尝试自动化我们的订单状态更新系统。这是我们现有的流程：

  1. 订单作为一个订单进入我们的系统（通过 FTP 文件），并分成 2 个或更多订单（插入 SplitOrdersHeader_tb）
  2. 生成的多个订单从 SplitOrders 表（标题和详细信息）发送到 ERP 系统
  3. ERP 系统为每个订单生成订单确认（插入 OrderConfirmationHeader_tb 和明细表）
  4. 收到所有拆分订单确认后需要更新原始单笔订单状态

以下是涉及的表格：

CREATE TABLE SplitOrdersHeader_tb(
    OriginalCustomerPONumber varchar(20),
    NewCustomerPONumber varchar(20),
    Company varchar(2),
    CustomerNumber varchar(10),
    OrderProcessed bit DEFAULT 0,
    OrderMoved bit DEFAULT 0,
    OrderConfirmationReceived bit DEFAULT 0
)

CREATE TABLE OrderConfirmationHeader_tb(
    MasterOrderNumber varchar(20),
    CustomerPONumber varchar(20),
    Company varchar(2),
    CustomerNumber varchar(10)
)

CREATE TABLE UpdateOtherSystem_tb(
    OriginalCustomerPONumber varchar(20)
)

一旦加载了订单确认，我在 OrderConfirmationHeader_tb 上有一个触发器，它会更新每个拆分订单的状态：

CREATE TRIGGER dbo.INSERT_Update_SplitOrderConfirmations_tg
    ON dbo.OrderConfirmationHeader_tb
    AFTER INSERT
    AS

    SET NOCOUNT ON

    BEGIN
        UPDATE SOH
        SET SOH.OrderConfirmationReceived = 1,
            SOH.MasterOrderNumber = LTRIM(RTRIM(I.MasterOrderNumber))
        FROM OrderSplitting.SplitOrdersHeader_tb SOH
            INNER JOIN inserted I ON SOH.CustomerNumber = I.Customer
                AND SOH.NewCustomerPONumber = I.Reference
                AND SOH.Company = I.Company 
                AND SOH.OrderProcessed = 1
                AND SOH.OrderMoved = 1
    END

我想要做的是在 SplitOrdersHeader_tb 上创建一个 UPDATE 触发器，它将： - 从原始 CustomerPONumber 计算拆分订单的数量 - 将 OrderConfirmationReceived 值的数量相加 - 如果 COUNT = SUM 然后将新记录插入到 UpdateOtherSystem_tb 中，前提是MasterOrderNumber 在 UpdateOtherSystem_tb 中尚不存在

我有这个，但感觉太笨拙了：

CREATE TRIGGER OrderSplitting.UPDATE_Update_WCO_Status_tg
    ON OrderSplitting.SplitOrdersHeader_tb
    AFTER UPDATE AS

    SET NOCOUNT ON

    BEGIN
        DECLARE @NEW_CUSTOMER_PO_NUMBER varchar(255),
                @ORIGINAL_CUSTOMER_PO_NUMBER varchar(255),
                @COUNT_OF_ORDER_HEADERS int,
                @TOTAL_CONFIRMED_ORDERS int

        SELECT @NEW_CUSTOMER_PO_NUMBER = NewCustomerPONumber
        FROM inserted

        SELECT @ORIGINAL_CUSTOMER_PO_NUMBER = OriginalCustomerPONumber,
                @COUNT_OF_ORDER_HEADERS = COUNT(*),
                @TOTAL_CONFIRMED_ORDERS = SUM(CAST(OrderConfirmationReceived as int))
        FROM OrderSplitting.SplitOrdersHeader_tb
        WHERE OriginalCustomerPONumber IN (SELECT OriginalCustomerPONumber
                                            FROM OrderSplitting.SplitOrdersHeader_tb
                                            WHERE NewCustomerPONumber = @NEW_CUSTOMER_PO_NUMBER)
        GROUP BY OriginalCustomerPONumber

        IF @COUNT_OF_ORDER_HEADERS = @TOTAL_CONFIRMED_ORDERS
            BEGIN
                BEGIN TRY
                    INSERT INTO OrderSplitting.UpdateOtherSystem_tb(OriginalCustomerPONumber)
                    VALUES(@ORIGINAL_CUSTOMER_PO_NUMBER)
                END TRY

                BEGIN CATCH
                    DECLARE @ERROR_MESSAGE varchar(MAX)
                    SET @ERROR_MESSAGE = ERROR_MESSAGE()

                    EXEC msdb..sp_send_dbmail
                            @recipients = <app_support>,
                            @subject = 'Update Trigger Error',
                            @body = @ERROR_MESSAGE
                END CATCH
            END
    END

标签： sql-servertriggerssql-server-2016

解决方案

我想我有一些东西，但想要一些额外的反馈，请：

CREATE TRIGGER OrderSplitting.UPDATE_Update_WCO_Status_tg
    ON OrderSplitting.SplitOrdersHeader_tb
    AFTER UPDATE AS

    SET NOCOUNT ON

    BEGIN TRY
        INSERT INTO OrderSplitting.UpdateOtherSystem_tb(OriginalCustomerPONumber)
            SELECT SOH.OriginalCustomerPONumber
            FROM OrderSplitting.SplitOrdersHeader_tb SOH
                INNER JOIN inserted I ON SOH.OriginalCustomerPONumber = I.OriginalCustomerPONumber
                LEFT OUTER JOIN OrderSplitting.UpdateOtherSystem_tb UOS ON SOH.OriginalCustomerPONumber = UOS.OriginalCustomerPONumber
            WHERE UOS.OriginalCustomerPONumber IS NULL
            GROUP BY SOH.OriginalCustomerPONumber
            HAVING COUNT(SOH.OriginalCustomerPONumber) = SUM(CAST(SOH.OrderConfirmationReceived as int))
    END TRY

    BEGIN CATCH
        DECLARE @ERROR_MESSAGE varchar(MAX)
        SET @ERROR_MESSAGE = ERROR_MESSAGE()

        EXEC msdb..sp_send_dbmail
        @recipients = <app_support>,
        @subject = 'Update Trigger Error',
        @body = @ERROR_MESSAGE
    END CATCH

推荐阅读