0

I created a CLR after trigger like this:

[Microsoft.SqlServer.Server.SqlTrigger(Name = "MyTrigger", Target = "[dbo].[MyTable]", Event = "AFTER INSERT, UPDATE")]

public static void TriggerName()
{
    SqlCommand command;
    SqlTriggerContext triggContext = SqlContext.TriggerContext;
    SqlPipe pipe = SqlContext.Pipe;
    SqlDataReader reader;

    using (SqlConnection connection
       = new SqlConnection(@"context connection=true"))
    {
        connection.Open();
        command = new SqlCommand(@"SELECT * FROM INSERTED;",
           connection);
        reader = command.ExecuteReader();

        while (reader.Read())
        {
            PopulateDataFromOneRow();
        }
        reader.Close();
        ProcessInsertedData();
    }
}

The trigger is supposed to process multiple row inserts. And it works for SQL querys such as 

   INSERT INTO MyTable SELECT * FROM AnotherTableWithSameSchema;

But when I tried to insert MyTable in Entity Framework using

    for(i = 1; i < 30; i++)
    {
        MyTable myTable = new MyTable();
        DoSomeThing();
        MyDbContext.MyTables.Add(myTable);
    }
    MyDbContext.SaveChanges();

The CRL trigger triggers once, but the inserted table only has the last row. I also tried 

    MyDbContext.MyTables.AddRange(aListOfMyTables);

It has the same problem.

And tried

    MyDbContext.Database.ExecuteSqlCommand("INSERT INTO MyTable(...) 
                                 VALUES (ROW1), (ROW2)... (ROW30);");

It doesn't seem to work.

How can I insert multiple rows at once in Entity Framework and let the trigger see all rows in the inserted table?

Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
amax
  • 1
  • It doesn't seem like you're doing anything special in your CLR trigger - why didn't you just write this as a normal T-SQL trigger? Doesn't really make a lot of sense to use CLR to do simple set-based operations in SQL Server ..... – marc_s Nov 14 '13 at 21:42
  • I do use an external math library (mathnet) and some complex calculation methods already written in C# in the CLR trigger. – amax Nov 15 '13 at 00:11
  • After doing some research, I found that it's better to do the calculation in SQL Server Service Broker instead of inside a trigger [link](http://stackoverflow.com/questions/425898/can-sql-clr-triggers-do-this-or-is-there-a-better-way) – amax Nov 15 '13 at 01:12

0 Answers0