检索在clr中触发触发器的sqlobject [英] Retrieve the sqlobject that fired the trigger in clr

问题描述

我有一个通用的clr触发器，可以在插入，更新，删除时将其附加到不同的表上。
例如

I have a generic clr trigger which can be attached to different tables on insert, update, delete. e.g.

[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对象？

... so it is not specific for a certain table. How can I find, inside the clr trigger, which is the sql object being updated by the trigger?

推荐答案

不，我发现了另一种方式：
基本上，您需要一个上一个触发器才能将会话上下文信息设置为某个值（例如）

No, I found another way: Basically you need a previous trigger to set a session context info to some value (e.g.)

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触发器可以访问上下文以检索此值并找出表。缺点（如果存在的话）是，如果在同一会话和事务处理和声明期间，带有触发器的两个表是修改的对象，则我不确定该上下文是否指向正确的表（例如，对视图）。但是在最常见的情况下，当以某种方式一次又一次地更新表时，它可以正常工作。

Then the clr trigger can access the context to retrieve this value and find out the table. The drawback (if it exists) is that if two tables with the those triggers are object of modification during the same session&transaction&statement, I'm not very sure if this context will point to the correct table (e.g. an update on a View). But in the most common scenarios, when tables are updated somehow one after another, it works ok.

这篇关于检索在clr中触发触发器的sqlobject的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！