A SQLCLR trigger should be given the parent object in the SQLTriggerContext - by rrozema

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


13
0
Sign in
to vote
ID 768358 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 10/23/2012 12:43:33 AM
Access Restriction Public

Description

From inside a SQLCLR trigger we have access to the action performed, the (windows) user performing the action, column count and the affected columns through the SqlContext.TriggerContext.  But the object that was affected is inaccessible. Please provide us also the parent object that was affected by the action. Adding a single int property "ParentID", holding the object_id for the trigger's parent object is sufficient for the purpose. 

Erland Sommarskog has already requested the same functionality back in 2006, but his request was for @@procID to be adapted for the purpose. His request was rejected on grounds of the designed functionality of @@procID (https://connect.microsoft.com/SQLServer/feedback/details/126461/-procid-returns-invalid-id-when-called-by-a-net-dml-trigger#tabs). The functionality he requested is however still very much needed. A quick search on the internet shows many more people need this, for example for creating a generic auditing trigger. There currently is no known alternative way to make one generic SQLCLR trigger assembly that is registered as a trigger on multiple objects in SQL server. In a T-SQL trigger this information is never needed, because you can not implement a T-SQL trigger that fires on multiple objects, i.e. the parent object is always known. But a SQLCLR trigger CAN be registered on multiple objects at the same time, i.e. here we do need to be given the parent object.
Sign in to post a comment.
Posted by rrozema on 2/28/2013 at 12:54 AM
And again closed as Won't fix. Why can MS play blind for the requests of their paying customers? The solution for this widely felt problem can be implemented by a very small addition to the SqlContext.TriggerContext object. This addition does not break any existing implementations, as it only extends the object with a previously non-existing attribute.

The "workaround" suggested by Umachandar is -besides the fact that is close to impossible to have to set the context_info before each and every update, insert and delete statement- not a solution since we can have triggers on the tables and views and thus can a single update, insert or delete statement modify objects that are not known to the issuer of the insert, update or delete and there can even be multiple objects changed by any single statement. We really do need a way to determine from the trigger context which object it is that was affected. PLEASE reconsider closing this (and all of the previous) requests for this feature.

Besides that, we already use context_info for a row-level-authorisation purpose, so for us it is not even a viable workaround without the trigger argument.
Posted by Microsoft on 2/27/2013 at 12:34 PM
Hello,
Thanks for your feedback. I understand the pain with this scenario but given the other high priority requests in our pipeline, we will not be implementing or considering this feature request.
You can use SET CONTEXT_INFO to pass the parent_object_id from the calling proc & retrieve the value using CONTEXT_INFO from the SQLCLR trigger. This is probably light weight than using temporary table.

--
Umachandar, SQL Programmability Team
Posted by Adam Machanic on 10/27/2012 at 11:00 AM
This is probably the fourth time this has been requested in a Connect item. Always closed as "Won't Fix," except for this one which was closed as "Fixed:" https://connect.microsoft.com/SQLServer/feedback/details/189211/get-proc-name-for-a-sqlclr-proc

(Of course, it never actually was.)

It's unfortunate that Microsoft refuses to help customers with this request. Clearly it's something that's very much needed.