Retrieve the sqlobject that fired the trigger in c

问题:

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()));


        }

    }

... 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?

回答1:

Hope this helps:

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

回答2:

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

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.