请求完善机票折扣触发器：年度累计消费超$1000享10%折扣

需求说明 #

需要创建SQL触发器，实现以下逻辑：

• 检查乘客在单个自然年度内购买机票的总金额
• 若该累计金额超过1000美元，为其下一张购买的机票自动登记10%的折扣

用户已编写触发器雏形，考虑用IF语句判断年度累计消费是否达标，以下是修正后的完整实现方案及原表结构说明。

修正后的完整触发器代码 #

CREATE TRIGGER dbo.Ticket_DiscountTrigger
ON dbo.Ticket
AFTER INSERT -- 仅在新增机票时触发
AS 
BEGIN
    SET NOCOUNT ON;

    -- 声明变量存储乘客标识、年度及累计消费金额
    DECLARE @docReference NVARCHAR(20),
            @documentType NCHAR(2),
            @nationality NCHAR(2),
            @currentYear INT,
            @totalAnnualFare DECIMAL(12,2);

    -- 游标处理批量插入场景，避免仅处理单条记录
    DECLARE ticket_cursor CURSOR FOR
        SELECT docReference, documentType, nationality, YEAR(issueDate)
        FROM inserted;

    OPEN ticket_cursor;
    FETCH NEXT FROM ticket_cursor INTO @docReference, @documentType, @nationality, @currentYear;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 计算乘客当前年度已消费总额（排除本次新增的机票）
        SELECT @totalAnnualFare = SUM(totalFare)
        FROM dbo.Ticket
        WHERE docReference = @docReference
          AND documentType = @documentType
          AND nationality = @nationality
          AND YEAR(issueDate) = @currentYear
          AND number NOT IN (SELECT number FROM inserted);

        -- 累计达标则给本次新增机票设置10%折扣
        IF @totalAnnualFare > 1000.00
        BEGIN
            UPDATE dbo.Ticket
            SET discount = totalFare * 0.10
            WHERE number IN (SELECT number FROM inserted
                             WHERE docReference = @docReference
                               AND documentType = @documentType
                               AND nationality = @nationality);
        END

        FETCH NEXT FROM ticket_cursor INTO @docReference, @documentType, @nationality, @currentYear;
    END

    CLOSE ticket_cursor;
    DEALLOCATE ticket_cursor;
END

代码关键点说明 #

• 触发时机：选择AFTER INSERT，仅在新增机票时执行折扣判断逻辑
• 批量兼容：用游标处理批量插入场景，避免遗漏部分记录
• 累计金额准确性：排除本次新增的机票，确保判断的是「之前的年度累计消费」
• 折扣计算：直接将折扣设为机票总价的10%，匹配需求中「下一张机票享10%折扣」的逻辑

原表结构（用户提供） #

Ticket表（机票表） #

CREATE TABLE Ticket(
    number INT NOT NULL, -- 机票编号（主键）
    issueDate DATE NOT NULL, -- 出票日期
    issueTime TIME(0) NOT NULL, -- 出票时间
    totalFare DECIMAL(12, 2) NOT NULL, -- 机票总价
    currency NCHAR(3) NOT NULL, -- 货币类型
    discount DECIMAL(12, 2) NOT NULL, -- 折扣金额
    docReference NVARCHAR(20) NOT NULL, -- 乘客证件编号
    documentType NCHAR(2) NOT NULL, -- 证件类型
    nationality NCHAR(2) NOT NULL, -- 国籍
    CONSTRAINT PK_Ticket_number PRIMARY KEY (number),
    CONSTRAINT FK_Ticket_Passenger FOREIGN KEY (docReference, documentType, nationality) 
        REFERENCES Passenger(docReference, documentType, nationality)
)

Passenger表（乘客表） #

CREATE TABLE Passenger(
    docReference NVARCHAR(20) NOT NULL, -- 证件编号
    documentType NCHAR(2) NOT NULL, -- 证件类型
    nationality NCHAR(2) NOT NULL, -- 国籍（组合主键字段）
    yearCreated DATE NOT NULL, -- 乘客记录创建日期
    title NCHAR(3) NULL, -- 称谓（如Mr/Ms）
    [name] NVARCHAR(15) NOT NULL, -- 名字
    middleNames NVARCHAR(100) NULL, -- 中间名
    surname NVARCHAR(15) NOT NULL, -- 姓氏
    passportReference NCHAR(10) NULL, -- 护照编号
    CONSTRAINT PK_Passenger PRIMARY KEY (docReference, documentType, nationality),
    CONSTRAINT FK_Passenger_Country FOREIGN KEY (nationality) REFERENCES Country(ISOalpha2Id),
    CONSTRAINT FK_Passenger_DocumentType FOREIGN KEY (documentType) REFERENCES DocumentType([type])
)

关于IF语句的可行性说明 #

用IF语句判断年度累计消费完全可行，但需注意以下细节：

• 必须排除本次新增的机票金额，否则会把当前订单算入累计，导致逻辑错误
• 必须兼容批量插入场景，不能仅处理单条记录
• 若涉及多货币，需统一转换为美元后再判断累计金额（当前表含currency字段，需额外增加汇率转换逻辑）

内容的提问来源于stack exchange，提问作者Astronomic02