SQL Server：创建触发器在Asset增改删时插入History.Asset并返回HistoryId

首先明确：触发器本身无法直接返回插入的HistoryId。触发器是依附于DML（增/删/改）操作的后台逻辑，执行上下文与发起DML的语句隔离，没有直接向调用者输出数据的通道。结合你的核心需求（关联Issue表记录变更通知），以下是两种可行的实现方案，优先推荐第一种：

方案一：用存储过程替代直接DML操作（最优解） #

通过封装存储过程，将「修改Asset、插入历史记录、获取HistoryId、插入Issue」这一系列操作放在同一个事务中，既能保证数据一致性，又能直接返回HistoryId，无需额外查询。

以SQL Server为例，示例代码如下：

1. 创建更新资产的存储过程 #

CREATE PROCEDURE dbo.HandleAssetUpdate
    @AssetId INT,
    @NewAssetName NVARCHAR(100),
    @NewOwner NVARCHAR(50), -- 示例：重要变更字段（所有者）
    @IssueTypeId INT, -- 变更对应的通知类型ID
    @HistoryId INT OUTPUT -- 输出参数，返回生成的HistoryId
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;

    BEGIN TRY
        -- 1. 更新Asset表的指定字段
        UPDATE dbo.Asset
        SET 
            AssetName = @NewAssetName,
            Owner = @NewOwner
        WHERE Id = @AssetId;

        -- 2. 插入历史记录并获取HistoryId
        INSERT INTO History.Asset (HistoryAction, Id)
        VALUES ('UPDATE', @AssetId);
        
        -- 用SCOPE_IDENTITY()获取当前会话、当前作用域的自增ID（避免受其他触发器影响）
        SET @HistoryId = SCOPE_IDENTITY();

        -- 3. 直接插入Issue表完成通知记录
        INSERT INTO dbo.Issue (Asset_Id, IssueType_Id, Resolved, History_Id)
        VALUES (@AssetId, @IssueTypeId, 0, @HistoryId);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW; -- 抛出错误，让调用方感知失败
    END CATCH
END

2. 调用存储过程获取HistoryId #

DECLARE @ReturnedHistoryId INT;
EXEC dbo.HandleAssetUpdate
    @AssetId = 123,
    @NewAssetName = '服务器-机房A',
    @NewOwner = '张三',
    @IssueTypeId = 3, -- 假设3是"所有者变更"的类型ID
    @HistoryId = @ReturnedHistoryId OUTPUT;

-- 直接拿到HistoryId，无需额外查询
SELECT @ReturnedHistoryId AS GeneratedHistoryId;

方案二：触发器+OUTPUT子句（兼容现有DML场景） #

如果必须保留直接对Asset表的DML操作，可以通过给History.Asset新增临时关联字段，结合OUTPUT子句间接获取HistoryId，但需要额外一次查询，且要修改表结构：

1. 修改History.Asset表，新增关联字段 #

ALTER TABLE History.Asset
ADD OperationGUID UNIQUEIDENTIFIER NULL;

2. 创建触发器 #

CREATE TRIGGER trg_Asset_AfterCRUD
ON dbo.Asset
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- 判断操作类型
    DECLARE @ActionType NVARCHAR(10);
    SET @ActionType = CASE
        WHEN EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED) THEN 'UPDATE'
        WHEN EXISTS(SELECT * FROM INSERTED) THEN 'INSERT'
        ELSE 'DELETE'
    END;

    -- 插入历史记录，带上临时生成的GUID
    INSERT INTO History.Asset (HistoryAction, Id, OperationGUID)
    SELECT
        @ActionType,
        COALESCE(i.Id, d.Id),
        COALESCE(i.OperationGUID, d.OperationGUID)
    FROM INSERTED i
    FULL JOIN DELETED d ON i.Id = d.Id;
END

3. 执行DML并获取HistoryId #

DECLARE @OpGUID UNIQUEIDENTIFIER = NEWID();
DECLARE @TargetHistoryId INT;

-- 更新Asset时，临时设置OperationGUID字段
UPDATE dbo.Asset
SET
    AssetName = '更新后的名称',
    OperationGUID = @OpGUID -- 临时赋值，用于关联历史记录
WHERE Id = 123;

-- 通过GUID查询对应的HistoryId
SELECT @TargetHistoryId = HistoryId
FROM History.Asset
WHERE OperationGUID = @OpGUID;

-- 插入Issue表
INSERT INTO dbo.Issue (Asset_Id, IssueType_Id, Resolved, History_Id)
VALUES (123, 3, 0, @TargetHistoryId);

-- 可选：清空Asset表的临时字段
UPDATE dbo.Asset
SET OperationGUID = NULL
WHERE Id = 123;

方案对比 #

结合你的核心需求（关联Issue表），优先选择存储过程方案，能最大限度保证数据一致性，同时简化业务逻辑。

内容的提问来源于stack exchange，提问作者Sakén