Determining the table updated inside of a SqlTrigger

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.

 

So the only solution I could come up with is comparing the table schema of the Inserted table against the known tables in the database. Maybe not the prettiest solution but it works. The first time a trigger fires the code caches the schema for each table in a dictionary. Afterwards I just compare the schema of the Inserted table against the cache of schema’s. Of course there is a catch, if your database contains two tables with identical structure they will produce the same schema, I change the table name, and the code will fail. As I don’t believe this is a common occurrence I decided I could live with this solution. But it anyone has a better solution that works with all existing databases I sure would like to hear about it [:)]

 

So the code is actually pretty simple:

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;
    }
}

 

Enjoy!

 

 

 


2 thoughts on “Determining the table updated inside of a SqlTrigger

  1. Looks like you have a fairly good solution, after spending a few days off goggling, i think your solution is probally the best around

    Thanks -

  2. it is pretty sad @@ProcId won’t work inside SQL CLR
    also TriggerContext provide no further information regarding table name.

    After searching a lot about it , your solution worked for me ..

    Thanks
    Ranu :-)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>