Accessing the triggered / parent table name inside a SQLCLR trigger

Question

I'm creating a relatively simple SQL CLR in C#.

The job of this CLR is to publish events to an Azure Event Grid Topic.

I would like to be able to call this particular CLR from any number of triggers for any number of tables (in actuality there will be few).

Given the core code below how does one accurately retrieve the name of the table that caused/fired the trigger? I have little clue where to start.

I would like to be able to remove the SqlTrigger attribute and somehow access the table name for the var table property.

// I Would like to leave the attribute commented out
//
//[Microsoft.SqlServer.Server.SqlTrigger(Name = "PublishToEventGridTrigger",
//     Target = "NamesOfThings", Event = "FOR UPDATE, INSERT, DELETE")]
public static void PublishToEventGridTrigger()
{
    // TODO - How should these settings be handled?     
    //        Not sure if config files are accessible... To test..
    // ***************************************************************

    string topicHost = "https://####.eventgrid.azure.net/api/events";
    string topicKey = "####";

    // TODO - Get Table name for this
    var table = "How Do I set This";
    string eventType = $"##.{table}.{SqlContext.TriggerContext.TriggerAction.ToString()}";

    // ***************************************************************

    try
    {
        // extract data involved in trigger
        // Create the Event object
        EventGridEvent evt = new EventGridEvent("QWDBEvent", eventType, new
                      TriggerData(SqlContext.TriggerContext, SqlContext.Pipe));

        // Publish the event
        Event.Publish(topicHost, topicKey, evt);
    }
    catch (Exception ex)
    {
        //TODO - how do we handle these through SQL CLR? 
        // Going with a fire-and-forget for now    
        SqlContext.Pipe.Send($"Failure firing {ex.Message}");        
    }
    SqlContext.Pipe.Send($"Trigger fired");
}

Answer 1

This is not natively supported, and not super easy to accomplish. I actually have a mock-up that does it but haven't had time to publish it. Certainly not straight-forward. For the moment, you can take a look at both answers to this question:

SQL CLR Trigger - get source table

And I will update this answer when I get my solution cleaned up and posted.

HOWEVER, while you say that there will be only a few tables that this will be applied to, please keep in mind that triggers are executed in the context of the transaction that the DML (or even DDL) statement is a part of. This has two consequences:

1. the longer the trigger takes, the longer the DML / DDL operation takes, hence the longer that the locks on the object(s) are being held. This can have an adverse effect on concurrency / performance. It makes many folks nervous enough to tie DML / DDL statements to web service calls to local (intranet / same network) services, but across the internet is introducing a dangerous, highly variable risk into a process that should be fairly simple. Of course, if you are using an Azure VM or Azure SQL Managed Instance, then maybe the latency is low-enough for this to be safe "enough". Either way, be very cautious! (To be clear: this risk is not mitigated by using a T-SQL trigger to execute a SQLCLR stored procedure; that's all still the same transaction)
2. if the trigger fails / throws an error, by default that will abort the transaction and rollback the DML (or DDL) operation. Is that intended behavior? Usually failure to log an event shouldn't abort the operation itself, right? So you would probably need to swallow the error (or at least write it to a text file, perhaps).

You should be able to decouple the DML/DLL operation from the logging piece by setting up Service Broker to handle calling the logging service. In this case you would:

1. Use a T-SQL trigger
   1. get the table name via:

      SELECT tab.[name]
      FROM   sys.objects tab
      WHERE  tab.[object_id] = (
                        SELECT trg.[parent_object_id]
                        FROM sys.objects trg
                        WHERE  trg.[object_id] = @@PROCID
                               );
      

   2. Gather any other info to log, possible from the INSERTED and/or DELETED tables (you won't have access to these after this; though you could possibly repackage the data in those two tables as XML to send as part of the Service Broker message -- e.g. SELECT * FROM inserted FOR XML RAW('ins'); )
   3. Queue a message for Service Broker including that gathered info
2. Use Service Broker
   1. will handle messages asynchronously from DML / DLL operations
   2. can execute SQLCLR stored procedure, passing in info gathered in T-SQL trigger, to call logging service (whether internal or external network)

Keep in mind (since you mentioned getting affected PK value from INSERTED and DELETED tables) that there can be multiple rows in those tables if the DML operation affected multiple rows (i.e. never assume a single row for DML operations).