.Net Core项目中AFTER INSERT触发器设主键插入记录报错求助

Let’s break down what’s causing this error and walk through how to fix it step by step.

First, the critical issue with your trigger #

Right now, your trigger updates every single row in Lookup.MyTable whenever a new record is inserted—this is almost certainly the root of your error. Without targeting only the newly added rows, you’re overwriting IDs for existing records, which will trigger primary key conflicts, data corruption, and the insert failure you’re seeing.

Step 1: Fix the trigger logic #

Update your trigger to only modify the rows that were just inserted, using SQL’s INSERTED system table to target specific records:

USE [MyDb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [Lookup].[MyTable_Insert] ON [Lookup].[MyTable]
AFTER INSERT
AS
SET NOCOUNT ON
UPDATE mt 
SET mt.ID = CONVERT(VARCHAR, mt.ID_AUTO)
FROM Lookup.MyTable mt
INNER JOIN INSERTED i ON mt.ID_AUTO = i.ID_AUTO;

This ensures only the new record’s ID gets set to the string version of its auto-generated ID_AUTO value, leaving existing data untouched.

Step 2: Configure EF Core to handle the database-generated primary key #

Since your ID is populated by the trigger after insert, EF Core needs to know it shouldn’t expect you to provide a value upfront, and that it should refresh the value after saving.

Option A: Using Fluent API (clean, separated configuration)

In your DbContext’s OnModelCreating method, add this setup for your MyTable entity:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<MyTable>()
        .Property(t => t.ID)
        .ValueGeneratedOnAdd(); // Tells EF Core this value is generated by the database on insert
}

Option B: Using Data Annotations (quick, inline setup)

Add the DatabaseGenerated attribute directly to your entity’s ID property:

public class MyTable
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public string ID { get; set; }
    public short ID_AUTO { get; set; }
    // Other properties for your table...
}

Step 3: Reload the entity if you need the generated ID immediately #

After saving changes, EF Core might still hold the temporary/empty ID value you sent initially. If you need to use the actual generated ID right away, reload the entity from the database:

var newRecord = new MyTable
{
    // Set all your other properties here (don't set ID)
};

context.MyTables.Add(newRecord);
await context.SaveChangesAsync();

// Reload to get the trigger-generated ID
await context.Entry(newRecord).ReloadAsync();

// Now newRecord.ID holds the correct value from the database

Why this works #

• The fixed trigger only touches the new record, eliminating primary key conflicts and data mess.
• EF Core’s configuration tells it to skip validating the ID on insert and fetch the generated value after saving.
• Reloading ensures your in-memory entity matches what’s actually stored in the database.

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