Retrieve The Sqlobject That Fired The Trigger In 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')]

Solution 1:

Hope this helps:

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

Solution 2:

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

CreateTRIGGER [dbo].[SET_MyContext_CONTEXT_INFO] 
 ON  [dbo].[MyTable]
 AFTER INSERT,DELETE,UPDATEASBEGINDECLARE@Ctxvarbinary(128)
SELECT@Ctx=CONVERT(varbinary(128), 'MyTable')
SET CONTEXT_INFO @CtxEND

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.