Fire trigger for every inserted row using SqlBulkCopy

Question

I am using SqlBulkCopy class to insert 50k rows at a time in table tbl_records I have set a After Insert trigger on this table and using following code

SqlBulkCopy SqlBc1 = new SqlBulkCopy(strConnString, SqlBulkCopyOptions.FireTriggers);

// Set DataReader For SqlBulkCopy

sqlComm = new SqlCommand(strQuery, sqlTemCon);
sqlComm.CommandTimeout = 3600000;
sqlComm.CommandType = System.Data.CommandType.Text;
SqlDataReader dReader = sqlComm.ExecuteReader();       
SqlBc1.WriteToServer(dReader);

But after execution of prog. It fire trigger for only First row out of 50k inserted

I wanna it should fire for every row. How can i do this??

Answer 1

Triggers never fire per-row. They fire for all rows of the corresponding DML statement. Rewrite your trigger so that it can cope with the INSERTED table containing many rows. This is best-practice and required practice anyway.

it is firing trigger for only first row inserted out of 50k rows

You must be misinterpreting the situation, maybe because you were unaware that triggers can contain multiple rows in the virtual tables.