Get/Set the table name without relying on column naming conventions

[GoogleCodeExporter]

The original code relies on DeriveTableNameFromKeyFieldName to determine 
the table name. which is commented as follows:

SQL CLR does not deliver the proper table name from either 
InsertedTable.TableName or DeletedTable.TableName, so we must use a 
substitute based on our key naming convention. 
We assume that in each table, the KeyFieldName = TableName + "Id". Remove 
this routine and its uses as soon as we can get the table name from the 
CLR.

This dependence needs to be removed. 

e.g. using sys.dm_tran_locks as per 
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1010082&SiteID=1

Original issue reported on code.google.com by ianper...@gmail.com on 13 Mar 2007 at 9:20

[GoogleCodeExporter]

Original comment by ianper...@gmail.com on 13 Mar 2007 at 1:01

• Added labels: Type-Enhancement
• Removed labels: Type-Defect

[GoogleCodeExporter]

Further investigations reveals:

Use of sys.dm_tran_locks requires the following permissions on the master 
database:
  GRANT VIEW SERVER STATE to [username]

It is not possible to pass the table name to the trigger method as CLR trigger 
methods cannot take any arguments.

Inserted.TableName and Deleted.TableName are not populated

Original comment by ianper...@gmail.com on 14 Mar 2007 at 8:54

• Changed state: Started

[GoogleCodeExporter]

You could put the table lookup in a stored proc and grant execute priveledges so
everyone can use it:

CREATE PROC dbo.spAuditTableLookup
(
    @TableName VARCHAR(127) OUTPUT
)
AS
SELECT @TableName = object_name(resource_associated_entity_id)
FROM sys.dm_tran_locks
WHERE
    request_session_id = @@SPID
    AND resource_type = 'OBJECT'
GO

GRANT EXEC ON spAuditTableLookup TO public AS sa

This way you don't need to grant everyone permission to view the server state 
stuff.
 My question is this: What happens when nested triggers execute?  Is there a way to
make sure that this proc returns the current table name?  Is there an ORDER BY 
clause
that would put all objects in the proper order so you could do a SELECT TOP 1 
instead?

Original comment by techj...@gmail.com on 11 Apr 2007 at 11:15

[GoogleCodeExporter]

thanks for the suggestion techjosh - I'll look into it and commit a new build 
to the 
respository as soon as I can.

In the meantime, I've added you as a project member - if you have already 
modified 
the code, please feel free to contribute.

Thanks again...

Original comment by ianper...@gmail.com on 14 Apr 2007 at 9:08