Trigger Windows Service when the new record insert in to DB [duplicate]

Question

Possible Duplicate:
Change Notification with Sql Server 2008

Am just wondering is there's anyway i can write a windows service in C# that will be trigger when the new record get inserted into Database.

And i would like to connect DB thru wcf also. Please give any ideas or suggestion.

Thanks in Advance.

Based on demo.b Instruction,here is the code.

SQL Database Details

---

My Database Name : MyWeb,Table Name : StoryItems, Columns: Location,Headline,Name,Genre.

---

 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
            // ***********Here am trying to retrieve the location and name column value            
            Location= (string)reader[9];
            Name= (String) reader[9];
            reader.Close();
            try
            {
                // try to pass parameter to windows service

                WindowsService param = new WindowService(InsertedValue1, InsertedValue2);
            }
            catch (Exception ex)
            {

            }



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

Some how it doesn't like column name, am not sure what am missing here."Trigger_Web" is my CLR SP name.

Answer 1

First you need to create a trigger application in visual studios.

File --> new --> project --> Database --> select Visual C# CLR database project.

It will prompt you to connect to a database. Once done, ensure your trigger application listen to record insertion on any table you like (you can read more about CLR app in visual studios here).

from steps in link above, add a trigger. your method should look like this:

[Microsoft.SqlServer.Server.SqlTrigger(Name = "GetTransaction", Target = "EvnLog", Event = "FOR INSERT")]
public static void GetTransaction()
{
    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 INSERTED", connection);
            reader = command.ExecuteReader();
            reader.Read();
            // get inserted value
            InsertedValue1 = (DateTime)reader[0];
            InsertedValue2 = (string)reader[9];
            reader.Close();
            try
            {
                // try to pass parameter to windows service

                WindowsService param = new WindowService(InsertedValue1,InsertedValue2)
            }
            catch (Exception ex)
            {

            }

        }

Note: GetTransaction is the name of the trigger you want to create, in this case Evnlog is the name of the table