some one please clarify me does CLR Trigger Support .Net 4.0?

Question

This thread is the continuation of the below thread Trigger Windows Service when the new record insert in to DB

I got the code from Demo.B suggested

public partial class Triggers
{
    // Enter existing table or view for the target and uncomment the attribute line
    [Microsoft.SqlServer.Server.SqlTrigger(Name = "Trigger_Web", Target = "StoryItems", Event = "FOR INSERT")]
    public static void Trigger_Web()
    {
        SqlCommand command;
        SqlTriggerContext triggerContext = SqlContext.TriggerContext;
        SqlPipe pipe = SqlContext.Pipe;
        SqlDataReader reader;

        if (triggerContext.TriggerAction == TriggerAction.Insert)
        {
            using (SqlConnection connection = new SqlConnection(@"context connection=true"))
            {
                connection.Open();
                command = new SqlCommand(@"SELECT * FROM StoryItems", connection);
                reader = command.ExecuteReader();
                reader.Read();

                // get inserted value
                string Name;
                string Location;
                Name = (string)reader[1];
                Location =(string)reader[2];
                reader.Close();
                //try
                //{
                //    // try to pass parameter to windows service

                //    //WindowsService param = new WindowService(Name, Location);
                //    //Do something if it pass

                //}
                //catch (Exception ex)
                //{

                //}

                // Replace with your own code
                SqlContext.Pipe.Send("Trigger FIRED");
            }
        }
    }
}

I am facing few problem when i start execute,first of all it ask me change the version from 4.0 to lower (so changed the version to 3.5 in the properties).

When i try to step into its not working and all the time it says deploy succeed and the output window shows "Canceled by User". Am not sure what going on the behind scene.

Please some one advise me how can i execute and see some results when the new row is inserted.

That's a seriously broken trigger - it attempts to read the contents of the entire table, will pick one row at random from that table, and assign the values of columns 1 & 2 from that row to `Name` and `Location`. Even if you *were* just reading the newly inserted rows, you've not accounted for the fact that an insert can produce multiple rows, and your trigger is fired once. — Damien_The_Unbeliever, Jan 17 '12 at 07:59

score 3 · Accepted Answer · answered Jan 17 '12 at 05:06

3

No, it does not. SQLCLR support for .Net 4.0 comes only in SQL Server 2012. However your Visual Studio knows better and will compile and deploy your DLLs as a .Net 2.0 assembly.

Other than that, doing Web calls from a trigger is a very bad idea. Your database will grind to a halt waiting on responses from WWW. Use a queueing mechanism and make the WWW call from process of your own, not from SQLCLR.

answered Jan 17 '12 at 05:06

Remus Rusanu

288,378
40
442
569

so what you guys suggesting,bcoz my rest of the project are built on .Net 4.0. – Usher Jan 17 '12 at 05:16
And am going to trigger Windows service from CLR Trigger – Usher Jan 17 '12 at 05:17
2

The best suggestion I have is to you read again my answer, more carefully. – Remus Rusanu Jan 17 '12 at 05:28

score 2 · Answer 2 · answered Jan 17 '12 at 05:07

2

No, Sql Server 2005-2008R2 not supporting .NET4 assemblies

answered Jan 17 '12 at 05:07

Oleg Dok

21,109
4
45
54

some one please clarify me does CLR Trigger Support .Net 4.0?

2 Answers2