April 24, 2010
SQL CLR Primer: part 3
In parts 1 and 2 of this series, I showed you how to create a CLR scalar-valued UDF and a CLR stored procedure, as well as how to enable CLR on your SQL Server instance, load your CLR objects into your database, and access your data from CLR using the calling context. Phew! In this installment, I want to demonstrate CLR triggers, and explain how to set the requisite permissions for assemblies that need to access external data.
As last time, you can launch Visual Studio and create a new SQL Server Project or load one you’ve saved previously. Right click on the project in Solution Explorer and choose Add » Trigger. One of the first things you may notice in this template relative to the previous two (although it starts commented out) is the difference in the attribute attached to the method. While the UDF and SP only required that the attribute be declared on the method, the CLR trigger attribute has certain properties which are required. Note that the other two do have properties, but none are required, and since we’re starting simply we’re leaving them out for now.
The Name property, for example, is present on all three of these, and although the trigger template includes it, it is not required for any of them. Feel free to remove it if you’d like to continue to keep things simple. The Target and Event properties, however, are required for triggers. For target, specify the name of the table or view on which the trigger will be applied. For event, specify the DML action (or actions) which activate it. The syntax for Event is exactly the same as it is in a CREATE TRIGGER statement. “FOR”, “AFTER”, or “INSTEAD OF” followed by “INSERT”, “DELETE”, and/or “UPDATE”, with commas between them if you specify more than one. Note that one thing all of this means is that you’ll have to recompile your code if, for example, a table name changes. And if you have two similarly structured tables that need two identical triggers, you’ll have to do your copy/pasting here. You won’t be able to take the shortcut of loading a single method and using two CREATE TRIGGER AS EXTERNAL NAME statements which specify the same function name.
So without further ado, here is an example insert trigger which takes the inserted values, bundled up as XML, and beams them to a remote server as an HTTP POST request:
public static void MyTrigger()
{
using (SqlConnection conn = new SqlConnection(“context connection=true”))
{
conn.Open();
SqlCommand comm = new SqlCommand(“SELECT * FROM inserted FOR XML RAW;”, conn);
object result = comm.ExecuteScalar();
if (result != null)
{
HttpWebRequest x = (HttpWebRequest)HttpWebRequest.Create(“http://example.com/receiver.aspx”);
x.Method = “POST”;
StreamWriter sw = x.GetRequestStream();
sw.Write(result.ToString());
sw.Close();
HttpWebResponse y = x.GetResponse();
if (y.StatusCode != HttpStatusCode.OK) throw new Exception(“HTTP RESPONSE ” + y.StatusCode.ToString());
}
}
}
As we saw last time, we’re using the connection string “context connection=true” in order to have our SQL executing in the calling context. This is extra important here, since – as we can with a standard SQL trigger – we’re using the inserted special table to access the rows that are being inserted. We use the SQL FOR XML clause in our SELECT statement to bundle everything up as a single XML string (easier than doing it ourselves), which we then write into our HTTP request.
You can compile this and load it into your database using the methods noted previously. However, because it accesses external data with the HTTP connection, you will need to load it with the External Access permission level. (Unsafe will work, too, but is less restrictive than what’s required for it to function.) I mentioned in passing in part 1 that this is only allowed if the database is set with the TRUSTWORTHY option off and if the database owner has the appropriate permissions. The trustworthy option is read-only in the SSMS GUI. To set it, you’ll need to issue the SQL command:
ALTER DATABASE [name] SET TRUSTWORTHY ON;
Easy enough. If your SQL deployment is very simple, you probably have the sa account as the owner of the database, in which case you’re all set, because sa has the requisite permissions. (A good thing, since permissions can’t be granted to sa.) If another user is the database owner, you can grant the permission with the following command:
USE master;
GRANT EXTERNAL ACCESS ASSEMBLY TO [name];
Or if the assembly were one that requires the Unsafe permission level:
USE master;
GRANT UNSAFE ASSEMBLY TO [name];
Note that the master database must be the calling context for the GRANT commands. Once that’s been done, you can load the assembly exactly as I’ve shown previously, and then create the trigger that calls it:
CREATE TRIGGER HTTPonInsert ON MyTable FOR INSERT
AS EXTERNAL NAME AssemblyName.ClassName.MyTrigger
(Unlike previous examples, I’ve used the method name from my example instead of “MethodName”, and similarly for the table name and DML commands.)
So, with the exception of the requirement that you specify the target table and DML commands in the CLR code, you can see that a trigger is not much more complex than a scalar UDF or a stored procedure. It can access the inserted and deleted tables exactly the same way a SQL trigger can. And you now know what settings need to be in place for the three different CLR assembly permission levels. A quick recap of what those levels are… “Safe” can only use managed code and cannot touch any data external to the calling database. “External access” can only use managed code, but can access data outside the database (other databases, file system, network, Internet, whatever). “Unsafe” can do anything, including executing unmanaged code.
Tune in next weekend for another CLR object (we’re getting into the crazy ones now!) and some more supporting info.
Santa said,
February 10, 2012 at 12:02 am
dani December 19th, 2008 12:54 am good post rlleay…thanx :)P.S something similar should be done for joomla