如何使用SqlBulkCopy C＃触发触发器 [英] How to fire trigger using SqlBulkCopy C#

问题描述

大家好，

我正在使用SqlBulkCopy将记录插入表中。我还添加了一个触发器，只是为了确定是否，如果没有记录然后插入。我的问题是在使用SqlBulkCopy时没有触发触发器。我还有一个触发器只是
将数据推入另一个表。

使用的触发器： -

创建触发器InsertintoMachinetbl  ;

   ON  MachineTbl

  INSTEAD OF INSERT

AS

BEGIN

 

  SET NOCOUNT ON;

  IF（不是EXISTS（选择1

     来自MachineTbl T，插入I $
  ;     WHERE T.FAC_CFG = I.FAC_CFG和T.SYS = I.SYS

      AND T.SYS_MCH_RL = I. SYS_MCH_RL

      AND I.FAC_CFG不为空）））
   INSERT INTO MachineTbl

      SELECT *

      FROM插入

结束

创建触发器Insertintohsttable

   ON  MachineTbl

   INSERT INSERT

AS

BEGIN

 

  INSERT INTO HstTbl

   SELECT FAC_CFG，SYS，SYS_MCH_RL，GETDATE（），MCH，WST_INR_PTL_AD，REC_UDT_USR

    FROM INSERTED

END

----------------------

以下三列是主键约束： -

FAC_CFG

SYS

SYS_MCH_RL

--------------------

使用SqlBulkCopy调用触发器的C＃代码： -

使用（SqlConnection sqlConnection = new SqlConnection（ConfigurationManager.ConnectionStrings [" DBConnectionString"]。ConnectionString））

                 {

                   sqlConnection.Open（）;

    的SqlTransaction事务= sqlConnection.BeginTransaction（）;

                &NBSP ;  SqlBulkCopy的sqlBulkCopy5 =新SqlBulkCopy的（SqlConnection的，SqlBulkCopyOptions.FireTriggers，交易）;

             &NBSP ;     sqlBulkCopy5.DestinationTableName = QUOT; MachineTbl英寸;

                &NBSP ;  sqlBulkCopy5.WriteToServer（ds.Tables [" MachineTblData"]）;

  }

--------

目前它没有抛出任何异常，但是我猜测触发器没有被调用，因为每当我通过SqlBulkcopy插入新记录时，新记录都不会被反映出来，如果我手动尝试插入语句，则以dB为单位插入。如有任何
解决方案，请告诉我。谢谢你。

解决方案

您好，

您的选项是  FireTriggers   SqlBulkCopyOptions失败，请使用

INSTEAD OF INSERT 触发或使用
MERGE 声明。

Hi all,

I am using SqlBulkCopy to insert records into the table. I also added one trigger , just to make sure whether, if there is no record then insert. My issue is trigger is not fired while using SqlBulkCopy. I also have one more trigger that is just to push the data into another table.

Triggers used :-

CREATE TRIGGER InsertintoMachinetbl 
   ON  MachineTbl
  INSTEAD OF INSERT
AS
BEGIN
 
 SET NOCOUNT ON;
 IF (NOT EXISTS (SELECT 1
      FROM MachineTbl T, inserted I
      WHERE T.FAC_CFG = I.FAC_CFG and T.SYS = I.SYS
      AND T.SYS_MCH_RL = I.SYS_MCH_RL
      AND I.FAC_CFG is not null))
   INSERT INTO MachineTbl
      SELECT *
      FROM inserted
END

CREATE TRIGGER Insertintohsttable
   ON  MachineTbl
   AFTER INSERT
AS
BEGIN
 
 INSERT INTO HstTbl
   SELECT FAC_CFG, SYS, SYS_MCH_RL, GETDATE(), MCH, WST_INR_PTL_AD, REC_UDT_USR
    FROM INSERTED
END

----------------------

the below three columns are primary key constraints:-

FAC_CFG
SYS
SYS_MCH_RL

--------------------

C# code to invoke trigger using SqlBulkCopy :-

using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString))
                {
                   sqlConnection.Open();
     SqlTransaction transaction = sqlConnection.BeginTransaction();
                   SqlBulkCopy sqlBulkCopy5 = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.FireTriggers, transaction);
                   sqlBulkCopy5.DestinationTableName = "MachineTbl";
                   sqlBulkCopy5.WriteToServer(ds.Tables["MachineTblData"]);
  }

--------

Currently it is not throwing any exception but, I guess trigger is not invoked because, whenever I insert new records through SqlBulkcopy , new records are not reflected, if I manually try insert statement in dB it is inserting. Please let me know for any solutions.Thanks in Advance.

解决方案

Hello,

Your options are since FireTriggers SqlBulkCopyOptions failed, use INSTEAD OF INSERT Triggers or use MERGE statement.

这篇关于如何使用SqlBulkCopy C＃触发触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！