SQL Server 触发器示例：订单状态更新

以下是一个复杂的 SQL Server 触发器脚本的例子：

CREATE TRIGGER trg_OrderStatusUpdate
ON Orders
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 获取更新前的订单状态
    DECLARE @OldStatus VARCHAR(50);
    SELECT @OldStatus = Status FROM deleted;

    -- 获取更新后的订单状态
    DECLARE @NewStatus VARCHAR(50);
    SELECT @NewStatus = Status FROM inserted;

    -- 检查订单状态是否变更为'已发货'
    IF @OldStatus <> '已发货' AND @NewStatus = '已发货'
    BEGIN
        -- 更新订单发货日期
        UPDATE Orders
        SET ShipDate = GETDATE()
        WHERE OrderID IN (SELECT OrderID FROM inserted);
    END

    -- 检查订单状态是否变更为'已取消'
    IF @OldStatus <> '已取消' AND @NewStatus = '已取消'
    BEGIN
        -- 删除相关的订单详情
        DELETE FROM OrderDetails
        WHERE OrderID IN (SELECT OrderID FROM inserted);

        -- 更新订单总金额为0
        UPDATE Orders
        SET TotalAmount = 0
        WHERE OrderID IN (SELECT OrderID FROM inserted);
    END
END

该触发器脚本针对订单表，当订单状态从非'已发货'变更为'已发货'时，触发器会更新订单的发货日期为当前日期；当订单状态从非'已取消'变更为'已取消'时，触发器会删除相关的订单详情，并将订单总金额更新为0。