您好，

您的选项是  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.