SQL CLR is pretty cool and lets you quickly do lots of things that are hard, if not impossible, to code in TSQL. But if you want to create generic code you sometimes need to know the context that the code is running under. The problem I had was creating a generic table update trigger and knowing the table that was updated.
During the execution of an update trigger you can get your hands on the new data using the Inserted temporary table so the data is there but there is no way of knowing the original table name, it just isn't exposed in any way. Well that was a bit of a problem and it turns out I wasn't the first one to run into this issue as web search pointed me to a number of similar questions but no answers. One possible solution I did find was using a <TableName>Id convention and making sure it was the first column every time. Not very pretty and not usable in my case as I have to work against an existing database.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Server;
publicpartialclassTriggers
{
// Enter existing table or view for the target and uncomment the attribute line
[SqlTrigger(Name = "Trigger1", Target = "TestTable", Event = "FOR UPDATE")]
publicstaticvoid Trigger1()
{
string schema = GetSchema("Inserted");
string tableName = DetermineTableNameFromSchema(schema);
SqlContext.Pipe.Send(string.Format("TableName is {0}", tableName));
}
privatereadonlystaticDictionary<string, string> _schemas = newDictionary<string, string>();
privatestaticstring DetermineTableNameFromSchema(string schema)
{
string result = "";
if (_schemas.Count == 0)
{
// First time, fill the collection with schema info
using (SqlConnection conn = newSqlConnection("context connection=true"))
{
string sql = "Select * from INFORMATION_SCHEMA.Tables where TABLE_TYPE = 'BASE TABLE'";
SqlDataAdapter ds = newSqlDataAdapter(sql, conn);
DataTable tables = newDataTable();
ds.Fill(tables);
foreach (DataRow row in tables.Rows)
{
string tableSchema = row["TABLE_SCHEMA"].ToString();
string tableName = row["TABLE_NAME"].ToString();
string fullName = string.Format("[{0}].[{1}]", tableSchema, tableName);
string tempSchema = GetSchema(fullName);
_schemas.Add(tempSchema, fullName);
}
}
}
_schemas.TryGetValue(schema, out result);
return result;
}
privatestaticstring GetSchema(string tableName)
{
string schema = "";
using (SqlConnection conn = newSqlConnection("context connection=true"))
{
string sql = string.Format("select * from {0}", tableName);
SqlDataAdapter ds = newSqlDataAdapter(sql, conn);
DataTable dt = newDataTable();
ds.FillSchema(dt, SchemaType.Source);
// Rename the table as we want to match it against the inserted table
dt.TableName = "Inserted";
// Always remove the primary key as it won't be present on the Inserted table
dt.PrimaryKey = null;
StringWriter sw = newStringWriter();
dt.WriteXmlSchema(sw, false);
schema = sw.ToString();
sw.Close();
}
return schema;
}
}