SQL CLR触发器-获取目标/表名 [英] SQL CLR Trigger - get Target / Table name

问题描述

跟踪列更改-多个目标/表的单个SQL CLR触发器

SQL CLR触发器：

是否可以通过CLR代码获取目标/表名？

Is there a way to get Target / Table name from CLR code?

目的：

我正在构建一个通用的SQL CLR触发器以跟踪多个表中的列更改。

I'm building a universal SQL CLR Trigger to track column changes in multiple Tables.

信息：

同一CLR触发器可以绑定到多个表。

The same CLR trigger can be bound to multiple Tables.

只要CLR触发器绑定到表，无论在CLR触发器属性中指定了什么目标/表，它都可以在任何表上触发。这意味着我可以创建1个CLR触发器，并将其用于所有需要更改跟踪的表。

As long as CLR Trigger is bound to a Table, it fires just fine on any Table no matter what Target/Table was specified in CLR Trigger Attribute. It means I can create 1 CLR Trigger and use it for all Tables that require change tracking.

问题出在触发器内调用表名/触发器名标识。
我尝试了所有DMV对象，但到目前为止，没有任何解决方法。顺便说一句，在CLR中无法访问@@ PROCID。

The problem is in calling table name / trigger name identification within the Trigger. I tried all DMV objects, so far nothing that solves the problem. Btw, @@PROCID is not accessible in CLR.

PS：我有一个解决方案，但是不能认为它是可靠和可靠的。

PS: I have a solution, but is can not be considered as nice and reliable.

推荐答案

public partial class Triggers
    {
        [SqlTrigger(Name = "TriggerName", Target = "TableName", Event = "FOR UPDATE")]
        public static void TriggerName ()
        {
            SqlTriggerContext triggerContext = SqlContext.TriggerContext;

            if (triggerContext.TriggerAction == TriggerAction.Update)
            {

                SqlConnection connection = new SqlConnection("Context Connection=true");
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "SELECT * FROM INSERTED,DELETED";
                connection.Open();
                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                if (SqlContext.TriggerContext.IsUpdatedColumn(reader.GetOrdinal("State")))
                {
                    reader.Read();
                    long MessageID = Convert.ToInt64(reader["MessageID"]);
                    int State = Convert.ToInt32(reader["State"]);
                    reader.Close();

                    if (State == 1)
                        FunctionName.SendMassage(MessageID); 
                }
            }
        }
    }

这篇关于SQL CLR触发器-获取目标/表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！