请求创建复杂SQL触发器：按到期日分配收款金额

Hey Andrea, let’s walk through building this payment allocation trigger step by step—this is a super common scenario for accounting systems, and we’ll use set-based SQL logic to keep it efficient and reliable.

First, let’s lock down the logic you described:

• When a new payment is added to the Collection table, we first apply the amount to unpaid invoices in order of their earliest DueDate first.
• If there’s any remaining amount after covering all unpaid invoices, we then apply it to unpaid installment payments, again sorted by earliest DueDate.
• We’ll update the PaidAmount field in both Invoice and InstallmentPayment tables to reflect the allocated funds.

To make the trigger fire when a new row is inserted into Collection, we use an AFTER INSERT trigger. Here’s the core structure:

CREATE TRIGGER trg_Collection_AllocatePayments
ON Collection
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON; -- Prevents extra row-count messages from breaking app integrations

    -- Allocation logic goes here
END

• AFTER INSERT: Ensures the trigger runs after the new payment is successfully saved, so we can safely read the new record from the special inserted system table.
• SET NOCOUNT ON: Critical for avoiding unexpected return values that might confuse applications interacting with your database.

We’ll use Common Table Expressions (CTEs) and window functions to calculate how much to allocate to each unpaid item. This is far more efficient than looping through records one by one.

Assumptions About Your Tables #

We’ll assume your tables have these key fields (adjust names if yours differ):

• Invoice: InvoiceID, CustomerID, DueDate, TotalAmount, PaidAmount
• InstallmentPayment: InstallmentID, InvoiceID, DueDate, InstallmentAmount, PaidAmount
• Collection: CollectionID, CustomerID, AmountReceived

Full Allocation Logic #

Add this inside the trigger body:

-- Get all new payments from the inserted table
WITH NewPayments AS (
    SELECT CollectionID, CustomerID, AmountReceived FROM inserted
),
-- Get all unpaid invoices for the customers with new payments
UnpaidInvoices AS (
    SELECT
        i.CustomerID,
        'Invoice' AS ItemType,
        i.InvoiceID AS ItemID,
        i.DueDate,
        (i.TotalAmount - ISNULL(i.PaidAmount, 0)) AS UnpaidBalance
    FROM Invoice i
    JOIN NewPayments np ON i.CustomerID = np.CustomerID
    WHERE (i.TotalAmount - ISNULL(i.PaidAmount, 0)) > 0
),
-- Get all unpaid installments for the same customers
UnpaidInstallments AS (
    SELECT
        i.CustomerID,
        'Installment' AS ItemType,
        ip.InstallmentID AS ItemID,
        ip.DueDate,
        (ip.InstallmentAmount - ISNULL(ip.PaidAmount, 0)) AS UnpaidBalance
    FROM InstallmentPayment ip
    JOIN Invoice i ON ip.InvoiceID = i.InvoiceID
    JOIN NewPayments np ON i.CustomerID = np.CustomerID
    WHERE (ip.InstallmentAmount - ISNULL(ip.PaidAmount, 0)) > 0
),
-- Combine invoices and installments, sorted by due date (invoices first, then installments)
AllUnpaidItems AS (
    SELECT * FROM UnpaidInvoices
    UNION ALL
    SELECT * FROM UnpaidInstallments
),
-- Calculate cumulative unpaid balances to see how much of the payment each item gets
CumulativeAllocations AS (
    SELECT
        np.CollectionID,
        au.ItemType,
        au.ItemID,
        au.UnpaidBalance,
        np.AmountReceived AS TotalPayment,
        -- Running total of unpaid balances sorted by due date
        SUM(au.UnpaidBalance) OVER (
            PARTITION BY au.CustomerID, np.CollectionID
            ORDER BY au.DueDate ASC, au.ItemType ASC
        ) AS CumulativeUnpaid
    FROM AllUnpaidItems au
    JOIN NewPayments np ON au.CustomerID = np.CustomerID
)
-- Update Invoice table with allocated amounts
UPDATE inv
SET PaidAmount = ISNULL(inv.PaidAmount, 0) + 
    CASE
        -- If the cumulative balance before this item is less than the payment
        WHEN (ca.CumulativeUnpaid - ca.UnpaidBalance) < ca.TotalPayment THEN
            -- Either pay the full unpaid balance, or whatever's left of the payment
            CASE
                WHEN ca.CumulativeUnpaid <= ca.TotalPayment THEN ca.UnpaidBalance
                ELSE ca.TotalPayment - (ca.CumulativeUnpaid - ca.UnpaidBalance)
            END
        ELSE 0 -- No allocation if payment is already exhausted
    END
FROM Invoice inv
JOIN CumulativeAllocations ca 
    ON ca.ItemType = 'Invoice' AND inv.InvoiceID = ca.ItemID;

-- Update InstallmentPayment table with remaining allocated amounts
UPDATE ip
SET PaidAmount = ISNULL(ip.PaidAmount, 0) + 
    CASE
        WHEN (ca.CumulativeUnpaid - ca.UnpaidBalance) < ca.TotalPayment THEN
            CASE
                WHEN ca.CumulativeUnpaid <= ca.TotalPayment THEN ca.UnpaidBalance
                ELSE ca.TotalPayment - (ca.CumulativeUnpaid - ca.UnpaidBalance)
            END
        ELSE 0
    END
FROM InstallmentPayment ip
JOIN CumulativeAllocations ca 
    ON ca.ItemType = 'Installment' AND ip.InstallmentID = ca.ItemID;

Key Logic Breakdown #

• We first group payments by customer to avoid applying one customer’s payment to another’s invoices.
• The CumulativeUnpaid window function calculates a running total of unpaid balances, so we can see exactly how much of the payment each item should receive (either full balance or remaining payment amount).
• We update both tables in separate statements, targeting only items that get a portion of the payment.

• Bulk Insert Support: This logic works for single or multiple payments inserted at once, since it uses the inserted table which contains all new rows.
• Transaction Safety: The trigger runs within the same transaction as the INSERT into Collection. If any part of the allocation fails, the entire payment insertion is rolled back—keeping your data consistent.
• Testing: Always test edge cases:
  • Payment exactly covers all unpaid invoices and installments.
  • Payment only covers part of the earliest invoice.
  • Payment exceeds total unpaid balance (remaining amount will be unallocated—you might want to add logic to track this as a prepayment if needed).
• Indexing: Add indexes on Invoice.DueDate, InstallmentPayment.DueDate, and CustomerID fields to speed up the allocation queries, especially if you have large datasets.

内容的提问来源于stack exchange，提问作者Andrea Mario Labate