How to get the TableName from within a clr trigger

Question

Post reply

How to get the TableName from within a clr trigger

jango_41

SSC Veteran

Points: 243
More actions
December 25, 2010 at 4:51 pm

#232299

Thank you great Helpers,
--this item was misposted, answer is found below.
written a c# trigger, got it running and it appears to work as expected.
It keeps track of a changelog.
It is allmost written independent of the table for which it has been written.
The only statement that is dependent is the TableName - that should appear in the changelog.
Looking around, I saw code to get that TableName like:
SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM sys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'", conn);
I tried this, but it appears not to work as generic as I would like.
I also tried to look into the TriggerContext, but I was not succesfull.
Who has the generic clue ?
Thank you in advance,
Edmond de Savornin Lohman
Utrecht, Netherlands
use @@SPID, access the system views.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

I'm curious... what does such a trigger do?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 2

Jeff Moden (12/26/2010)
I'm curious... what does such a trigger do?

The idea is usually to write a CLR trigger which can be attached to many objects, auditing the same information for each. Unfortunately, limitations of the current design make this hard to do reliably, as the author of this thread is discovering.

Jango, consider voting for Adam Machanic's Connect item here:

https://connect.microsoft.com/SQLServer/feedback/details/265346/provide-properties-to-help-with-common-tasks-in-clr-triggers

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

SQLkiwi (12/26/2010)
Jeff Moden (12/26/2010)
I'm curious... what does such a trigger do?
The idea is usually to write a CLR trigger which can be attached to many objects, auditing the same information for each. Unfortunately, limitations of the current design make this hard to do reliably, as the author of this thread is discovering.
Jango, consider voting for Adam Machanic's Connect item here:
https://connect.microsoft.com/SQLServer/feedback/details/265346/provide-properties-to-help-with-common-tasks-in-clr-triggers

Other than the commonality you speak of, is there an advantage to using a CLR over "standard" triggers?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)