Trigger Windows Service when the new record insert in to DB

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.

Solution 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

Solution 2

Use something like an Extended Stored Procedure in SQL server, that calls your C# class/executable, which then can execute the service.

You can also call command line functions from the triggers on the on_insert event on the table, which can start/stop a service, or run an exe or batch file.

Some ideas: http://www.sqlservercentral.com/Forums/Topic960855-392-1.aspx

And http://msdn.microsoft.com/en-us/library/ms189799.aspx

Comments

[ta.speot.is]

I appreciate what you've shown is an example (which pretty much solves the problem), but... I imagine if you're only getting one value then it should be SELECT TOP 1 * otherwise the SQL Server may continue to spool results. And reader should be .Dispose'd, not just .Close'd.

[Usher]

Thanks a lot demo.b,what's gonna happen my windows service is running on remote location or my sql server running on remote location.In that case,WCF or any service is the only option to communicate and trigger,please correct me if am wrong.

[Usher]

Quite Interesting warning i got when i try to create a new project "Note: If you do not have the .Net Framework Version 3.5 Installed on your development computer,you must install it if you want to develop SQL CLR assemblies for sql server 2005 or Sql Server 2008 support only those assemblies that target the 2.0,3.0 or 3.5 version of the .Net Framework. You specify the .Net Framework version in the Project properties. Press F1 for more info."

[demo.b]

it doesn't matter where ur windows service is running from. it could be on the same server as the sql server or on a remote server. if it's on the same box, in visual studios, right click on trigger project, select properties. Under database settings, change permission level to "Save". if it is a remote box, you will set to "unsafe' or "External". Once you are done developing the trigger application, you will need to deploy to database by right clicking and selecting deploy. deployment requires some privillages on database.

[demo.b]

before you deploy to database, please read this to prepare database geekswithblogs.net/rasyadi/archive/2005/11/18/60459.aspx. To anser the other question, you need vs 2008/2010 and sql server 2005/2008

[Usher]

Thanks again demo.b, pretty much followed your instruction and so far going good,i have couple of queries. 1)@"SELECT * FROM INSERTED", what is this query does ? or Inserted is suppose to be my table name ? 2) InsertedValue1 = (DateTime)reader[0]; InsertedValue2 = (string)reader[9]; I believe InsertedValue1 and InsertedValue2 suppose to be my Column Name,please correct me.

[demo.b]

[Microsoft.SqlServer.Server.SqlTrigger(Name = "GetTransaction", Target = "EvnLog", Event = "FOR INSERT")] "Name" is the method i created so you can give it any name. "Target" is the tablename in my example "EvnLog" is the tablename. "Event" is for the type of response the trigger is listening to, in the example insert.

[demo.b]

The query @"SELECT * FROM INSERTED", selects newly inserted rows from the EvnLog table. In my example each row contains about 10 columns so only select the one i need. (DateTime)reader[0] gets the value in first column (which is a date) and (DateTime)reader[9] gets the value in column 10.