时间戳

首页 > 解决方案 > 而不是插入触发器不适用于我通过 C# 代码插入

c# - 而不是插入触发器不适用于我通过 C# 代码插入

问题描述

我有以下触发器：

CREATE TRIGGER insert_or_update_AccountNews
ON AccountNews
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
        @AccountNumber bigint,
        @NewsId int,
        @TariffPlan nvarchar(1024)

    SELECT @AccountNumber = INSERTED.AccountNumber,
           @NewsId = INSERTED.NewsId,
           @TariffPlan = INSERTED.TariffPlan
    FROM INSERTED

    IF EXISTS (SELECT NewsId FROM [AccountNews] 
               WHERE AccountNumber = @AccountNumber AND NewsId = @NewsId)
        UPDATE [AccountNews]
        SET TariffPlan  = @TariffPlan
        WHERE AccountNumber = @AccountNumber
          AND NewsId = @NewsId
    ELSE
        INSERT INTO [AccountNews] (NewsId, AccountNumber, TariffPlan)
            SELECT @NewsId, @AccountNumber, @TariffPlan
END;

如您所见，我有一张桌子，名为AccountNews. 它具有以下列：

AccountNumber, NewsId, TariffPlan

这个想法是当我在表中插入一些东西时，触发器将确定数据是否存在（我有唯一的约束AccountNumberand NewsId）或不存在。如果数据不存在 - 插入，否则 - 更新。

它可以通过 SQL 控制台完美运行，例如：

insert into  AccountNews (NewsId, AccountId, TariffPlan) 
values (12345, 777777, 'Hello world');

insert into  AccountNews (NewsId, AccountId, TariffPlan) 
values (12345, 777777, 'Hello world 2');

接下来，我有这个 C# 代码来插入数据：

DataTable table = await ReadAsStringAsync(file, newsId);

var connectionString = config.GetConnectionString("MyDbConnection");

using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlBulkCopy bcp = new SqlBulkCopy(connection))
{
    connection.Open();

    bcp.BatchSize = 1000;

    bcp.DestinationTableName = "[dbo].[AccountNews]";

    bcp.ColumnMappings.Add("NewsId", "NewsId");
    bcp.ColumnMappings.Add("AccountNumber", "AccountNumber");
    bcp.ColumnMappings.Add("TariffPlan", "TariffPlan");
    bcp.ColumnMappings.Add("Date", "Date");

    await bcp.WriteToServerAsync(table);
}

在这种情况下，我看不到触发器的结果。当我加载一些已经在我的数据库中的数据时，我有一个唯一的约束异常。

标签： c#sql-servertriggers

解决方案

要创建仅限 UPSERT 的表，您可以添加如下触发器：

use tempdb
go

drop table if exists AccountNews 

create table AccountNews
( 
  AccountNumber bigint,
  NewsId int,
  TariffPlan nvarchar(1024),
  constraint pk_AccountNews 
    primary key (AccountNumber, NewsId)
)
go
CREATE TRIGGER insert_or_update_AccountNews
ON AccountNews
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    merge AccountNews as target
    using (select * from inserted) as source
    on (target.AccountNumber = source.AccountNumber and target.NewsId = source.NewsId)
    when matched then
        update set TariffPlan = source.TariffPlan
    when not matched then
        insert (AccountNumber, NewsId, TariffPlan)
        values (source.AccountNumber, source.NewsId, source.TariffPlan);
END;

或没有 MERGE （不允许在单个批次中重复）：

CREATE TRIGGER insert_or_update_AccountNews
ON AccountNews
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    with q as
    (
      select a.*, i.TariffPlan NewTariffPlan
      from AccountNews a 
      join inserted i
        on a.AccountNumber = i.AccountNumber
       and a.NewsId = i.NewsId
    )
    update q set TariffPlan = NewTariffPlan;

    insert into AccountNews(AccountNumber,NewsId,TariffPlan)
    select AccountNumber,NewsId,TariffPlan
    from inserted i
    where not exists
    (
       select * 
       from AccountNews a
       where a.AccountNumber = i.AccountNumber
         and a.NewsId = i.NewsId
    );
END;

go

并且您选择使用SqlBulkCopyOptions进行触发器和约束检查，通常应该在从应用程序批量加载时执行此操作，因为绕过约束或触发器需要对表具有 ALTER TABLE 权限。

推荐阅读