检索激发触发器在CLR中的SQLObject(Retrieve the sqlobject that

我有一个可以连接到在插入不同的表通用CLR触发，更新，删除。 例如

[Microsoft.SqlServer.Server.SqlTrigger(Event = "FOR UPDATE, INSERT, DELETE")]
public static void TriggerHandle()
{

    DataTable dataTable = new DataTable("Test");
  SqlTriggerContext myContext = SqlContext.TriggerContext;

    try
    {
        using (SqlConnection conn = new SqlConnection(CONNECTION_STRING))
        {
            conn.Open();
            SqlCommand sqlComm = new SqlCommand();

            sqlComm.Connection = conn;

            switch (myContext.TriggerAction)
            {
                case TriggerAction.Insert:
                    sqlComm.CommandText = "Select *,'inserted' as operation from inserted";
                    break;

                case TriggerAction.Update:
                    sqlComm.CommandText = "Select *,'updated' as operation from inserted";

                    break;

                case TriggerAction.Delete:
                    sqlComm.CommandText = "Select *,'deleted' as operation from deleted";
                    break;

            }
            dataTable.Load(sqlComm.ExecuteReader(), LoadOption.Upsert);
            SqlContext.Pipe.Send(String.Format("The datatable is populated with {0} rows ", dataTable.Rows.Count.ToString()));


        }

    }

...所以它不是具体的某个表的。 我如何才能找到，CLR的触发，这是SQL对象被触发更新里面？

Answer 1:

希望这可以帮助：

SELECT OBJECT_NAME(parent_object_id) [object]
FROM sys.objects 
WHERE name = OBJECT_NAME(@@PROCID)

Answer 2:

不，我找到了另一种方式：基本上，你需要一个触发的会话上下文信息设置为某个值（例如）

Create TRIGGER [dbo].[SET_MyContext_CONTEXT_INFO] 
 ON  [dbo].[MyTable]
 AFTER INSERT,DELETE,UPDATE
 AS 
 BEGIN
DECLARE @Ctx varbinary(128)
SELECT @Ctx = CONVERT(varbinary(128), 'MyTable')
SET CONTEXT_INFO @Ctx
 END

GO
EXEC sp_settriggerorder @triggername=N'[dbo].[SET_MyContext_CONTEXT_INFO]',@order=N'First', @stmttype=N'DELETE'
GO
EXEC sp_settriggerorder @triggername=N'[dbo].[SET_MyContext_CONTEXT_INFO]', @order=N'First', @stmttype=N'INSERT'
GO
EXEC sp_settriggerorder @triggername=N'[dbo].[SET_MyContext_CONTEXT_INFO]', @order=N'First', @stmttype=N'UPDATE'
GO

然后CLR触发器可以访问上下文检索该值，并找出该表。 缺点（如果存在的话）是，如果与这些触发器两个表是相同的会话和交易＆声明中变更处理的对象，我不是很肯定，如果这种情况下将指向正确的表（例如，在一个视图中的更新）。 但是，在最常见的情况，当表被莫名其妙地更新了一个又一个，它工作正常。

文章来源: Retrieve the sqlobject that fired the trigger in clr