Creating a CLR trigger couldn’t be easier:

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.