EF Core中Payment表触发器报‘Invalid object name inserted’错误求助

使用EF Core为MRPayments.Payments表创建触发器后，在应用中插入新付款记录时，调用上下文保存更改抛出错误：

Microsoft.EntityFrameworkCore.Update:
Error: An exception occurred in the database while saving changes for context type 'ApplicationDbContext'.
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.

Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name 'MRPayments.inserted'

最初的触发器代码 #

CREATE TRIGGER trg_Payment_Log
ON MRPayments.Payments
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @Action nvarchar(10)

    IF EXISTS (SELECT * FROM [MRPayments].[inserted])
    BEGIN
        IF EXISTS (SELECT * FROM [MRPayments].[deleted])
            SET @Action = 'UPDATE'
        ELSE
            SET @Action = 'INSERT'
    END
    ELSE
        SET @Action = 'DELETE'

    INSERT INTO MRLogs.Payment_Logs (PaymentId, Action, OldValues, NewValues, LogDate)
    SELECT 
        CASE 
            WHEN @Action = 'DELETE' THEN [MRPayments].[deleted].Id 
            ELSE [MRPayments].[inserted].Id 
        END,
        @Action,
        CASE 
            WHEN @Action = 'UPDATE' THEN dbo.GetOldValues_Payment([MRPayments].[deleted].Id) 
            ELSE NULL 
        END,
        CASE 
            WHEN @Action = 'INSERT' THEN dbo.GetNewValues_Payment([MRPayments].[inserted].Id) 
            ELSE dbo.GetNewValues_Payment([MRPayments].[deleted].Id) 
        END,
        GETDATE()
    FROM [MRPayments].[inserted]
    FULL OUTER JOIN [MRPayments].[deleted] ON [MRPayments].[inserted].Id = [MRPayments].[deleted].Id
END
GO

尝试过的操作 #

1. 将触发器中带架构的[MRPayments].[inserted]/[MRPayments].[deleted]改为仅inserted/deleted，错误依旧
2. 在ApplicationDbContext中配置触发器关联，配置后问题未解决：

modelBuilder
    .Entity<Payment>()
    .ToTable(t => t.HasTrigger("trg_Payment_Log"));

修改后的完整代码（仍报错） #

CREATE TRIGGER trg_Payment_Log
ON MRPayments.Payments
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @Action nvarchar(10)

    IF EXISTS (SELECT * FROM  inserted)
    BEGIN
        IF EXISTS (SELECT * FROM deleted)
            SET @Action = 'UPDATE'
        ELSE
            SET @Action = 'INSERT'
    END
    ELSE
        SET @Action = 'DELETE'

    INSERT INTO MRLogs.Payment_Logs (PaymentId, Action, OldValues, NewValues, LogDate)
    SELECT 
        CASE 
            WHEN @Action = 'DELETE' THEN deleted.Id 
            ELSE inserted.Id 
        END,
        @Action,
        CASE 
            WHEN @Action = 'UPDATE' THEN dbo.GetOldValues_Payment(deleted.Id) 
            ELSE NULL 
        END,
        CASE 
            WHEN @Action = 'INSERT' THEN dbo.GetNewValues_Payment(inserted.Id) 
            ELSE dbo.GetNewValues_Payment(deleted.Id) 
        END,
        GETDATE()
    FROM inserted
    FULL OUTER JOIN deleted ON inserted.Id = deleted.Id
END

GO

CREATE FUNCTION dbo.GetOldValues_Payment(@PaymentId uniqueidentifier)
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @OldValues nvarchar(max)

    SELECT @OldValues = 
        'PaymentStatus: ' + COALESCE(CONVERT(nvarchar(max), deleted.PaymentStatus), 'NULL') + CHAR(13) + CHAR(10) +
        'ApplicationUserId: ' + COALESCE(CONVERT(nvarchar(max), deleted.ApplicationUserId), 'NULL') + CHAR(13) + CHAR(10) +
        'PaymentValuePLN: ' + COALESCE(CONVERT(nvarchar(max), deleted.PaymentValuePLN), 'NULL') + CHAR(13) + CHAR(10)
    FROM deleted
    WHERE deleted.Id = @PaymentId

    RETURN @OldValues
END

GO

CREATE FUNCTION GetNewValues_Payment (@PaymentId uniqueidentifier)
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @NewValues nvarchar(max)

    SELECT @NewValues = CONCAT(
        'PaymentStatus: ', COALESCE(NEW.PaymentStatus, 'NULL'), CHAR(13), CHAR(10),
        'ApplicationUserId: ', COALESCE(NEW.ApplicationUserId, 'NULL'), CHAR(13), CHAR(10),
        'PaymentValuePLN: ', COALESCE(CAST(NEW.PaymentValuePLN AS nvarchar(max)), 'NULL')
    )
    FROM inserted AS NEW
    WHERE NEW.Id = @PaymentId

    RETURN @NewValues
END

Go

错误核心原因是触发器中调用的自定义函数无法访问inserted/deleted临时表——这两个临时表仅在触发器主体范围内有效，不能被外部函数直接引用。

1. 重写自定义函数 #

将函数改为直接接收字段参数，而非在函数内部查询临时表：

CREATE FUNCTION dbo.GetOldValues_Payment(
    @PaymentStatus nvarchar(50),
    @ApplicationUserId uniqueidentifier,
    @PaymentValuePLN decimal(18,2)
)
RETURNS nvarchar(max)
AS
BEGIN
    RETURN CONCAT(
        'PaymentStatus: ', COALESCE(@PaymentStatus, 'NULL'), CHAR(13), CHAR(10),
        'ApplicationUserId: ', COALESCE(CONVERT(nvarchar(max), @ApplicationUserId), 'NULL'), CHAR(13), CHAR(10),
        'PaymentValuePLN: ', COALESCE(CONVERT(nvarchar(max), @PaymentValuePLN), 'NULL')
    )
END

GO

CREATE FUNCTION dbo.GetNewValues_Payment(
    @PaymentStatus nvarchar(50),
    @ApplicationUserId uniqueidentifier,
    @PaymentValuePLN decimal(18,2)
)
RETURNS nvarchar(max)
AS
BEGIN
    RETURN CONCAT(
        'PaymentStatus: ', COALESCE(@PaymentStatus, 'NULL'), CHAR(13), CHAR(10),
        'ApplicationUserId: ', COALESCE(CONVERT(nvarchar(max), @ApplicationUserId), 'NULL'), CHAR(13), CHAR(10),
        'PaymentValuePLN: ', COALESCE(CONVERT(nvarchar(max), @PaymentValuePLN), 'NULL')
    )
END
GO

2. 修改触发器调用逻辑 #

在触发器中直接从inserted/deleted表读取字段值，传递给新函数：

CREATE TRIGGER trg_Payment_Log
ON MRPayments.Payments
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO MRLogs.Payment_Logs (PaymentId, Action, OldValues, NewValues, LogDate)
    SELECT 
        ISNULL(inserted.Id, deleted.Id) AS PaymentId,
        CASE
            WHEN inserted.Id IS NOT NULL AND deleted.Id IS NOT NULL THEN 'UPDATE'
            WHEN inserted.Id IS NOT NULL THEN 'INSERT'
            ELSE 'DELETE'
        END AS Action,
        CASE WHEN deleted.Id IS NOT NULL THEN dbo.GetOldValues_Payment(deleted.PaymentStatus, deleted.ApplicationUserId, deleted.PaymentValuePLN) ELSE NULL END,
        CASE WHEN inserted.Id IS NOT NULL THEN dbo.GetNewValues_Payment(inserted.PaymentStatus, inserted.ApplicationUserId, inserted.PaymentValuePLN) ELSE NULL END,
        GETDATE() AS LogDate
    FROM inserted
    FULL OUTER JOIN deleted ON inserted.Id = deleted.Id;
END
GO

3. 验证EF Core配置 #

保留原有的EF Core配置即可：

modelBuilder
    .Entity<Payment>()
    .ToTable(t => t.HasTrigger("trg_Payment_Log"));

关键说明 #

• inserted和deleted是触发器专属临时表，仅在触发器执行上下文内存在，自定义函数无法直接访问，这是原错误的根本原因。
• 添加SET NOCOUNT ON;可避免触发器返回的行数干扰EF Core的变更跟踪逻辑。

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