Creating Triggers Using Managed Code in SQL Server 2005

[arjitmal]

SCENARIO 1 :

I have 3 projects in my solution:

1) Web Project (Presentation Layer)

2) Class Library Project (Business Logic)

3) Class Library Project (Data Access Layer)

Following are the dependencies among these 3 projects:

1->2->3 (It means I have referenced PL in BL and BL in DAL.

In 3(Data Access Layer) i have written a SQL trigger and in the same trigger i am also calling a function of 2(Business Logic) as i have referenced it already. The code looks like following:

 [Microsoft.SqlServer.Server.SqlTrigger(Name = "Trigger1", Target = "test", Event = "FOR INSERT")]
        public static void Trigger1()
        {
            SqlTriggerContext triggContext = SqlContext.TriggerContext;     
            getData obj = new getData(); //getData is a class in (Business Logic)

            DataSet insertTriggerDataSet = new DataSet();

            if (triggContext.TriggerAction == TriggerAction.Insert)
            {
                using (SqlConnection conn = new SqlConnection("context connection=true"))
                {
                    conn.Open();
                    SqlCommand sqlComm = new SqlCommand();
                    SqlPipe sqlP = SqlContext.Pipe;

                    sqlComm.Connection = conn;

                    sqlComm.CommandText = "select * from test";
                    //sqlP.Send(sqlComm.CommandText);
                    //sqlP.ExecuteAndSend(sqlComm);

                    SqlDataAdapter adapter = new SqlDataAdapter(sqlComm.CommandText, conn);
                    adapter.SelectCommand.CommandType = CommandType.Text;

                    called= "trigger.cs";
                    adapter.Fill(insertTriggerDataSet);

                }
            }        

            obj.fetch(insertTriggerDataSet); // fetch is a method in class getData in Business Logic

      }

Now the problem is, that is i am registering my assembly of (Data Access Layer) in SQL Server 2005 then due to its all dependencies i am unable to do so (as Data Access Layer is dependent on Business Layer and Business Layer on Presentation Layer, and Presentation Layer is internally dependent on many .NET assemblies)

SCENARIO 2

If i convert my data access layer to SQL Server project then it is impossible to give reference of any other project in my Data Layer as SQL Server Project doesn't accepts references other than SQL Server projects.

My need is to call a method X() of Business Layer in that Trigger (in Data Access Layer) and then in that method X() I have to call a method Y() of my presentation Layer.

Can anyone please suggest me any solution or alternative for this problem?...

Also let me make it clear that I am trying to build an auto update data representation control (like grid view) so whenever a new record is inserted in a SQL table that trigger should work and then it should update the data in used data representation control in presentation layer without any button click or similar event, thats why i am calling all functions internally to create an event while that trigger executes. (Reference Example: Facebook Wall, Twitter stream)

Any help will be appreciated...

Thank you.