CLR Trigger

Creating a CLR trigger couldn’t be easier:

image

In VS 2008 Create a new SQL Server Project, then create a new trigger (right click project name, add, trigger.

Fill in the attribute’s properties:

[Microsoft.SqlServer.Server.SqlTrigger(Name = "PackageDeleteTrigger", Target = "Packages", Event = "FOR DELETE")]
public static void PackageDeleteTrigger()

and write your code, for example:

[Microsoft.SqlServer.Server.SqlTrigger(Name = "PackageDeleteTrigger", Target = "Packages", Event = "FOR DELETE")]
public static void PackageDeleteTrigger()
{
    // Replace with your own code
    SqlContext.Pipe.Send("Trigger FIRED");

    using (SqlConnection conn = new SqlConnection("context connection=true"))
    {
        conn.Open();
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from deleted";
            using (SqlDataReader rdr = cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    string path = rdr.GetString(2);
                    string fullpath = Path.Combine(@"c:\temp", path);
                    File.Delete(fullpath);

                    SqlContext.Pipe.Send("Deleted file " + fullpath);
                }
            }
        }
    }
}

Running the project (F5) will compile it, and deploy the assembly to your SQL server. The test.sql script will be automatically executed. Of course you should make your code a bit more robust, checking for possible exceptions. Otherwise the CLR exception will pass to SQL server, and your record won’t be deleted. It will all depend on your situation whether this is good or bad.

I’ll discuss the security problems in the next post.

About Gaston

MCT, MCSD, MCDBA, MCSE, MS Specialist
This entry was posted in Geen categorie. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s