on error resume next
MM_FlashCanPlay = ( IsObject(CreateObject("ShockwaveFlash.ShockwaveFlash." & MM_contentVersion)))
| http://mjxads.internet.com/RealMedia/ads/click_lx.ads/intm/webdev/www.15seconds.com/focus/netfeatures/1208183551/accessunit/OasDefault/HP_Polyserve_GEMS_1kg/au_HP_SQLserver.html/37616134303566303438653361386130?HPSC/Door/38979 |
http://mjxads.internet.com/RealMedia/ads/click_lx.ads/intm/webdev/www.15seconds.com/focus/netfeatures/1208183551/accessunit/OasDefault/HP_Polyserve_GEMS_1kg/au_HP_SQLserver.html/37616134303566303438653361386130?HPSC/Link/38984
Download this whitepaper for a ready-made solution that delivers comprehensive infrastructure optimization in accordance with Microsoft’s APO model using HP PolyServe software in conjunction with HP BladeSystem c-Class products and Microsoft SQL Server Enterprise Edition.http://mjxads.internet.com/RealMedia/ads/click_lx.ads/intm/webdev/www.15seconds.com/focus/netfeatures/1208183551/accessunit/OasDefault/HP_Polyserve_GEMS_1kg/au_HP_SQLserver.html/37616134303566303438653361386130?HPSC/Link/38984
http://mjxads.internet.com/RealMedia/ads/click_lx.ads/intm/webdev/www.15seconds.com/focus/netfeatures/1208183551/accessunit/OasDefault/HP_Polyserve_GEMS_1kg/au_HP_SQLserver.html/37616134303566303438653361386130?HPSC/Link/38985
Download this whitepaper for an efficient approach to SQL Server deployment and management with HP PolyServe Software for Microsoft SQL Server. The software supports both Microsoft SQL Server 2000 and SQL Server 2005, and includes a component called Matrix Server, which is shared data clustering software. It allows groups of servers and storage to work and be managed together flexibly to satisfy application requirements. http://mjxads.internet.com/RealMedia/ads/click_lx.ads/intm/webdev/www.15seconds.com/focus/netfeatures/1208183551/accessunit/OasDefault/HP_Polyserve_GEMS_1kg/au_HP_SQLserver.html/37616134303566303438653361386130?HPSC/Link/38985
http://mjxads.internet.com/RealMedia/ads/click_lx.ads/intm/webdev/www.15seconds.com/focus/netfeatures/1208183551/accessunit/OasDefault/HP_Polyserve_GEMS_1kg/au_HP_SQLserver.html/37616134303566303438653361386130?HPSC/Link/38986
The phenomenal success of Microsoft's SQL Server database has led to "SQL Server sprawl," which can be costly due to inefficient use of hardware, software, and administrative resources. Learn how HP's PolyServer Software for Microsoft SQL Server can consolidate SQL Server environments and improve resource availability. http://mjxads.internet.com/RealMedia/ads/click_lx.ads/intm/webdev/www.15seconds.com/focus/netfeatures/1208183551/accessunit/OasDefault/HP_Polyserve_GEMS_1kg/au_HP_SQLserver.html/37616134303566303438653361386130?HPSC/Link/38986
http://mjxads.internet.com/RealMedia/ads/click_lx.ads/intm/webdev/www.15seconds.com/focus/netfeatures/1208183551/accessunit/OasDefault/HP_Polyserve_GEMS_1kg/au_HP_SQLserver.html/37616134303566303438653361386130?HPSC/Link/38987
SQL Server 2005 is quickly becoming the database solution of choice. The task of SQL Server 2005 migration need not be as difficult, time-consuming, or costly as one might anticipate. Software tools like HP PolyServe Migration Manager can dramatically reduce the complexity of migration and slash deployment time by up to 66 percent. http://mjxads.internet.com/RealMedia/ads/click_lx.ads/intm/webdev/www.15seconds.com/focus/netfeatures/1208183551/accessunit/OasDefault/HP_Polyserve_GEMS_1kg/au_HP_SQLserver.html/37616134303566303438653361386130?HPSC/Link/38987 |
| http://mjxads.internet.com/RealMedia/ads/click_lx.ads/intm/webdev/www.15seconds.com/focus/netfeatures/1208183551/accessunit/OasDefault/HP_Polyserve_GEMS_1kg/au_HP_SQLserver.html/37616134303566303438653361386130?HPSC/Door/38979 |  |
- http://www.15seconds.com/files/041006.zip
One of the excellent features provided by SQL Server 2005 (code named Yukon) is its integration with the .NET CLR which makes it possible for us to author triggers, stored procedures, user defined functions, and create other database objects using a managed language such as VB.NET, C#, and so on. This approach provides a number of benefits such as increased productivity, significant performance gains and the ability to leverage the features of .NET Code Access Security to prevent assemblies from performing certain operations and so on. In this article, we will take a look at this new CLR integration feature and learn how to create triggers in SQL Server using a managed language. Along the way, we will also learn how the features of .NET code access security can be leveraged to better control the assembly execution environment. Finally, we will discuss when to use T-SQL and when to use a .NET language when creating SQL Server triggers.
.NET CLR and SQL Server Integration
In previous versions of SQL Server, database programmers were limited to using Transact-SQL when creating server side objects such as triggers, stored procedures, and user defined functions and so on. But now with the integration of SQL Server with .NET CLR, it opens up a whole avenue of opportunities. Before we talk about the features of .NET CLR integration with SQL Server, let us understand the limitations of T-SQL when it comes to creating server side objects.
Transact-SQL (T-SQL) is an extension of the Structured Query Language as defined by the International Standards Organization (ISO) and the American National Standards Institute (ANSI). Using T-SQL, database developers can create, modify and delete databases and tables, as well as insert, retrieve, modify and delete data stored in a database. T-SQL is specifically designed for direct data access and manipulation. While T-SQL can be very useful for data access and management, it is not a full-fledged programming language in the way that Visual Basic .NET and C# are. For example, T-SQL does not support arrays, strongly typed objects, collections, for each loops, bit shifting or classes and so on. While some of these constructs can be simulated in T-SQL, managed code based languages such as VB.NET or C# have first-class support for these constructs. Now that we have understood the limitations of T-SQL, let us talk about the advantages of .NET CLR integration with SQL Server.
With CLR integration, things have changed dramatically. The CLR provides the execution environment for all the server side objects that are created using a .NET language. This means the database developers can now perform tasks that were impossible or difficult to achieve with T-SQL alone. Especially when working with large amounts of server code, developers can easily organize and maintain your code investments. By allowing the code to run under the control of .NET CLR, you can also leverage the code access security features of .NET. For example, before executing code, the CLR can check to see if the code is safe. This process is known as "verification." During verification, the CLR performs several checks to ensure that the code is safe to run. For example, the code is checked to ensure that no memory is read that has not be been written to. The CLR will also prevent buffer overflows. Now that we have had a complete overview of the .NET CLR integration, let us understand the steps to be followed for creating a trigger in VB.NET.
Creating a Trigger using Managed Code in SQL Server
As you may know, triggers are executed as the result of a user action against a table, such as an INSERT, UPDATE or DELETE statement. To create a trigger using a managed language such as C# or VB.NET, you need to go through the following steps.
- Create a .NET class and implement the functionality of the extended trigger within that class.
- Compile that class to produce a .NET assembly.
- Register that assembly in SQL Server using the Create Assembly statement.
- Create trigger definitions. As part of this, you also associate the trigger with the actual methods in the assembly. Once this is done, the triggers are configured and can be invoked automatically like any other triggers.
In the next section, we will take an in-depth look at the above steps and understand what it takes to create a trigger using C#. Implementation of .NET class that will act as an extended Trigger
Before we go onto creating the .NET class that will implement the functionalities of the trigger, let us create a simple table named Users using the following DDL statement.
CREATE TABLE Users
(UserName Varchar (100) )
Now that we have created the table, let us create the C# class that implements the functionalities required of the trigger. Towards this end, we will create a C# class named Users and modify its code to look like the following code.
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
public class Users
{
public static void InsertTrigger()
{
SqlTriggerContext triggerContext = SqlContext.GetTriggerContext();
SqlPipe sqlPipe = SqlContext.GetPipe();
SqlCommand command = SqlContext.GetCommand();
if (triggerContext.TriggerAction ==
System.Data.Sql.TriggerAction.Insert)
{
command.CommandText = "SELECT * FROM INSERTED";
sqlPipe.Execute(command);
}
}
}
Let us walk through the above code. To execute .NET code in SQL server, you need to reference the System.Data.Sql and System.Data.SqlServer namespaces. Then we declare a class named Users that is mainly used to implement the trigger functionalities for the Users table. Then we get reference to the current trigger context by invoking the GetTriggerContext method of the SqlContext class. The SqlTriggerContext object enables the code to access the virtual table that's created during the execution of the trigger. This virtual table stores the data that caused the trigger to fire. The SqlPipe object enables the extended trigger to communicate with the external caller. To get reference to the SqlPipe object, we invoke the GetPipe method of the SqlContext class. Once we have reference to the SqlPipe object, we can then return tabular results and messages to the client. In this example, the SqlTriggerContext object is used to first determine if the trigger action was an insert operation. If so, then the contents of the virtual trigger table are retrieved and sent to the caller. Compilation of .NET class to create a .NET Assembly
Now that we have created the C# class, let us compile that class to produce a .NET assembly, which can then be registered with SQL Server. The following screenshot shows how to compile the C# class to produce the .NET assembly. 
As shown in above screenshot, we need to reference the sqlaccess.dll since our code uses the classes contained in the System.Data.SqlServer namespaces. Registering the assembly in SQL Server
When writing managed code, the deployment unit is called an assembly. An assembly is packaged as a DLL or executable (EXE) file. While an executable can run on its own, a DLL must be hosted in an existing application. Managed DLL assemblies can be loaded into and hosted by Microsoft SQL Server. To load an assembly into SQL Server, you need to use the Create Assembly statement.
CREATE ASSEMBLY Users
FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\Test\CLRProcedures\CS\Users.dll'
The FROM clause specifies the pathname of the assembly to load. This path can either be a UNC path or a physical file path that is local to the machine. The above statement will register the assembly with the SQL Server. Note that the assembly name should be unique within a database. Once you load the assembly, a copy of the assembly is loaded into SQL Server. After that, if you want to make changes to the assembly, you need to drop the assembly first and then reregister that with SQL Server again. To drop an assembly from SQL Server, you need to use the Drop Assembly statement. For example, to drop the assembly that we created earlier, we need to use the following command.
DROP ASSEMBLY Users
Loading an assembly into Microsoft SQL Server is the first step in exposing the functionality that the assembly provides. Now that we have loaded the assembly, the next step is to associate an extended trigger to a specific method of the class that is contained in the assembly. Creating Trigger Definitions
In this step, we will create an extended trigger using the Create Trigger statement. SQL Server 2005 supports a new clause named External Name that allows you to reference a method in the registered assembly. By referencing this method, we hook the trigger to that method in the assembly.
CREATE TRIGGER InsertTrigger
ON Users
FOR INSERT
AS
EXTERNAL NAME
Users:[Users]::InsertTrigger
For the purposes of this example, we will use the InsertTrigger method in the Users class. In the above code, External Name clause uses the following syntax.
[Name of the Assembly]:[Name of the Class]::[Name of the Method]
Now that the trigger is created, let us test the trigger by using the following Insert statement that inserts a row into the Users table.
Insert Users Values('TestUser')
You will see the output as shown in the following screenshot.  |