本文介绍了如何使用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为单位插入。如有任何
解决方案,请告诉我。谢谢你。



解决方案

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.

解决方案


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

08-04 02:15
查看更多