Question
Craigster on Tue, 07 Mar 2006 15:04:53
I am writing a generic trigger in VS 2005 that selects records from the inserted table, and updates an audit table. I am, however, unable to retrieve the name of the table that the insert occurred on. I am using the following code to select the records, and obtain the name.. Can anyone offer any alternatives to accomplishing this task? Thanks in advnace for any help you can provide.
Craig
SqlDataAdapter tableLoader = new SqlDataAdapter("SELECT * FROM inserted", connection);
DataTable insertedTable = new DataTable();
tableLoader.Fill(insertedTable);
string insertedTableName = insertedTable.TableName;
Replies
Simon Sabin on Fri, 10 Mar 2006 09:51:12
I don't know the answer, however I would strongly suggest that you would be better off scripting a trigger for each table that did the auditing. Having that level of data access in your CLR trigger is likely to perform worse than a pure TSQL trigger.
This is not a definitive statement just a word of warning.
Craigster on Fri, 10 Mar 2006 18:06:32
Thanks for the tip... I didn't think the performance would be that much worse. We were trying to create an auditing solution generic enough to handle all auditing, rather than writing a trigger for each table.
Thanks, again, for the response!
Craig
DanNova on Mon, 27 Mar 2006 18:44:07
I believe you can do this with the eventdata() function. You may need to do some XQuery to get the specific value you want because this will return an XML document describing the event. I think it's a good idea to have this one trigger to catch all your audited updates. Simple single object to manage. Simple = good!
Derek Comingore on Tue, 28 Mar 2006 01:54:12
EVENTDATA returns data only when referenced directly inside of a DDL trigger.
The requirements here are for Insert Actions, thus its a DML trigger not DDL.
Actually, this is not an easy question, but I believe the answer lies in the fact that DML triggers are table specific objects for this reason. What do I mean by this? Consider this...
[Microsoft.SqlServer.Server.SqlTrigger(Name = "tri_InsertAudit", Target = "Test", Event = "FOR INSERT")]
The target attribute can only accept 1 table name (to my knowledge). And even before CLR triggers were around, even in TSQL a trigger was always declared such as...
CREATE TRIGGER trigger_name
ON <schema_name, sysname, Sales>
So DML triggers have always been thought of as a table-level entity. I believe it is this manner of thinking that is the reason there is no obvious way to extract the affected tables name, because the creators of DML triggers assume you will know the table name. So what is my answer, that to meet your auditing requirements with a DML trigger you must make it specific per Target.
I have thought up some "off the wall" solutions before for similiar tasks which usually end up involving heavy tsql usage, information schemas, and system table queries but to be honest if you have to go to this extent its probably not a good idea in the first place :)
At the least do this:
TSQL DML Trigger:
Create Trigger dbo.testtrig
On test
For Insert
As
Begin
Insert LogTable
Select I.*, 'testTable' As [Table] From inserted I
End
Derek Comingore on Tue, 28 Mar 2006 02:04:59
Really then, in review, the anser is No it cannot be done.
Why? Because you cannot create one trigger for multiple tables. Thus this violates your whole intention which was to have one object for all auditing purposes.
probably not the answer you were hoping for, but I hope this helps,
Derek
Simon Sabin on Tue, 28 Mar 2006 17:54:54
You could have the same core function that is called by a wrapper. Having a wrapper for each table and being attached the relevant table.
m_shane_tx on Tue, 05 Dec 2006 15:27:02
I created a generic AuditTrigger in C# that is not Table Specific. And about half way down it has a way to retireve the TableName using SQL. ;-)
using System;
using
System.Data;using
System.Data.SqlClient;using
Microsoft.SqlServer.Server;public
partial class Triggers{
//A Generic Trigger for Insert, Update and Delete Actions on any Table[Microsoft.SqlServer.Server.
SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")] public static void AuditTrigger(){
SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context string TName; //Where we store the Altered Table's Name string User; //Where we will store the Database Username DataRow iRow; //DataRow to hold the inserted values DataRow dRow; //DataRow to how the deleted/overwritten values DataRow aRow; //Audit DataRow to build our Audit entry with string PKString; //Will temporarily store the Primary Key Column Names and Values here using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection{
conn.Open();
//Open the Connection //Build the AuditAdapter and Mathcing Table SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM TestTableAudit WHERE 1=0", conn); DataTable AuditTable = new DataTable();AuditAdapter.FillSchema(AuditTable,
SchemaType.Source); SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert Command for us //Get the inserted values SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn); DataTable inserted = new DataTable();Loader.Fill(inserted);
//Get the deleted and/or overwritten valuesLoader.SelectCommand.CommandText =
"SELECT * from DELETED"; DataTable deleted = new DataTable();Loader.Fill(deleted);
//Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire) 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);TName = cmd.ExecuteScalar().ToString();
//Retrieve the UserName of the current Database User SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn);User = curUserCommand.ExecuteScalar().ToString();
//Adapted the following command from a T-SQL audit trigger by Nigel Rivett //http://www.nigelrivett.net/AuditTrailTrigger.html SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@"SELECT c.COLUMN_NAMEfrom INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = '"
+ TName + @"'and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME"
, conn); DataTable PKTable = new DataTable();PKTableAdapter.Fill(PKTable);
switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table
{
case TriggerAction.Update:iRow = inserted.Rows[0];
//Get the inserted values in row formdRow = deleted.Rows[0];
//Get the overwritten values in row formPKString = PKStringBuilder(PKTable, iRow);
//the the Primary Keys and There values as a string foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns{
if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed{
//Build an Audit EntryaRow = AuditTable.NewRow();
aRow[
"ActionType"] = "U";//U for UpdateaRow[
"TableName"] = TName;aRow[
"PK"] = PKString;aRow[
"FieldName"] = column.ColumnName;aRow[
"OldValue"] = dRow[column.Ordinal].ToString();aRow[
"NewValue"] = iRow[column.Ordinal].ToString();aRow[
"ChangeDateTime"] = DateTime.Now.ToString();aRow[
"ChangedBy"] = User;AuditTable.Rows.InsertAt(aRow, 0);
//Insert the entry}
}
break; case TriggerAction.Insert:iRow = inserted.Rows[0];
PKString = PKStringBuilder(PKTable, iRow);
foreach (DataColumn column in inserted.Columns){
//Build an Audit EntryaRow = AuditTable.NewRow();
aRow[
"ActionType"] = "I";//I for InsertaRow[
"TableName"] = TName;aRow[
"PK"] = PKString;aRow[
"FieldName"] = column.ColumnName;aRow[
"OldValue"] = null;aRow[
"NewValue"] = iRow[column.Ordinal].ToString();aRow[
"ChangeDateTime"] = DateTime.Now.ToString();aRow[
"ChangedBy"] = User;AuditTable.Rows.InsertAt(aRow, 0);
//Insert the Entry}
break; case TriggerAction.Delete:dRow = deleted.Rows[0];
PKString = PKStringBuilder(PKTable, dRow);
foreach (DataColumn column in inserted.Columns){
//Build and Audit EntryaRow = AuditTable.NewRow();
aRow[
"ActionType"] = "D";//D for DeleteaRow[
"TableName"] = TName;aRow[
"PK"] = PKString;aRow[
"FieldName"] = column.ColumnName;aRow[
"OldValue"] = dRow[column.Ordinal].ToString();aRow[
"NewValue"] = null;aRow[
"ChangeDateTime"] = DateTime.Now.ToString();aRow[
"ChangedBy"] = User;AuditTable.Rows.InsertAt(aRow, 0);
//Insert the Entry}
break; default: //Do Nothing break;}
AuditAdapter.Update(AuditTable);
//Write all Audit Entries back to AuditTableconn.Close();
//Close the Connection}
}
//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values //and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......" public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow)
{
string temp = String.Empty; foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed{
temp =
String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString()].ToString(), ">,"));}
return temp;}
}
Hope this helps. Enjoy!!!!!!!
ckimmel on Wed, 15 Aug 2007 21:53:40
Man! I really thought you had it with:
SELECT object_name(resource_associated_entity_id) FROM sys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'"
This works great unless the darn tables have cascade delete on them. If cascade delete is on, you'll get the last table in the delete chain. Shoot!!!
Any other ideas?
nielsb on Thu, 16 Aug 2007 09:19:57
Chris,
have you actually tried to deploy the trigger m_shane_tx posted? As the trigger doesn't have a target, the deployment will fail (at least it does it for me). I.e AFAIK when you create a DML trigger, you have to have a target, so you can not hav a generic trigger for all tables.
Niels
m_shane_tx on Thu, 16 Aug 2007 20:27:48
I'm not sure of the difference between your deployment process and mine, but I can deploy that trigger just fine using Visual Studio (without a target). Maybe you have extra constraint for triggers on your server or something, but for me that trigger works as is.
In answer to ckimmel, I am not sure why it doesn't work as is. Since the cascade should cause the trigger to fire on the next table which should do what you want.
Does the trigger not fire at each level of your cascading delete?
m_shane_tx on Thu, 16 Aug 2007 20:40:26
On further thought I better understand what is most likely happening for ckimmel the transaction is probably locking multiple tables at once ( i.e all tables the cascading delete touches). So "SELECT object_name(resource_associated_entity_id) FROM sys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'" is really going to return a list of tablenames and the ExexcuteScalar call only shows you 1 of them. You need to further constrain the 'where' clause which I am not sure is even possible. Do a google search for sys.dm_trans_locks and see what of value you can filter by to get a single value returned each time the trigger fires.
Like I said though I am not sure it can be done.
MShaneHorn
m_shane_tx on Thu, 16 Aug 2007 21:53:08
nielsb try following the deployment process for the VB based generic audit trigger at the following address. http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk
This was the original article that I got my inspiration from for the C# trigger I wrote. I then modified it to automatically retrieve the table name instead of requiring a specific table naming structure.
Trying using the TSQL they use to deploy the VB trigger to deploy my modified C# version.
mshanehorn
nielsb on Fri, 17 Aug 2007 07:25:57
OK, I misunderstood what you were doing. I thought you somehow manged to create one generic trigger in the database, without associating the trigger with a specific table. Having read the article and the following paragraph:
<<<<<<<<<<<<<<<<<<
Now associate the CLR trigger routine with the "ADDRESS" table. With the generic trigger, this is all the code you'll need to audit a table (you can stick this into your standard template for table creation):
>>>>>>>>>>>>>>>>>
and the following code-snippet:
<<<<<<<<<<<<<<<<<<
create trigger Audit_ADDRESS
on ADDRESS for insert, update, delete
as external name [AuditCommon].[AuditCommon.Triggers].AuditCommon
>>>>>>>>>>>>>>>>>>
I see that you actually are associating the trigger with table(s).
Niels
Alphi on Thu, 04 Oct 2007 07:54:03
Man your code for getting the table name is seriously flawed.
"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);"
that means that if anyone connects to your database who isnt an administrator the trigger will cause a runtime error "user does not have permission"
I tried using EXECUTE AS 'dbo' in the stored procedure.
I even put the code into its own stored procedure and tried Execute AS with a whole heap of logins and users.
the microsoft documentation is incorrect,
if you use the following code
GRANT VIEW SERVER STATE TO [loginname];
GO
GRANT VIEW SERVER STATE TO [loginname];
GO
GRANT SELECT ON OBJECT:

GO
GRANT EXECUTE ON OBJECT:

and then put sys.dm_tran_locks or sp_lock into a stored procedure and run it EXECUTE AS [loginname]
you still get a permissions error..
the code will only work if you run it straight from the SQL Server Management Studio when your connected as an administrator.
which is why this code is flawed.
if you check username
does anyone know how I can GRANT the correct amount of permissions to my Login so that it can run either sys.dm_tran_locks or sp_lock
if you run this code it doesnt work
Crerate Procedure [dbo].[usp_Test]
WITH EXECUTE AS 'dbo'
BEGIN
select USER_NAME()
select * from sys.dm_tran_locks
END
If you run this code from the management studio it does work
select USER_NAME()
In both cases User_Name returns as 'dbo'
WTF!!!!!!!!!!!!!!!!!!!!!!
Just my 2c
gagis on Thu, 15 Nov 2007 19:51:29
Hey guys,
A workaround has been documented here:
http://blogs.msdn.com/sqlclr/archive/2005/11/21/495438.aspx
and outlined below:
At this moment, there is no easy way of obtaining the table name from a CLR trigger. Below I give two ways of working around this.
1st solution:
- Add a String argument to the CLR Trigger to pass the table name
- Create a user defined function for each table that will have this trigger registered on.
public partial class Triggers
{
private static void Trigger(String tableName)
{
// You can now differentiate the trigger's behavior depending on the value of tableName
SqlContext.Pipe.Send("This is a trigger on " + tableName);
}
[Microsoft.SqlServer.Server.SqlFunction]
public static void Function_A()
{
Trigger("A");
}
[Microsoft.SqlServer.Server.SqlFunction]
public static void Function_B()
{
Trigger("B");
}
}
Then you register a trigger on table A as
CREATE TRIGGER TA
ON A
[FOR UPDATE | AFTER INSERT ...]
AS EXTERNAL NAME Assembly.Triggers.Function_A
and you register a trigger on table B as
CREATE TRIGGER TB
ON B
[FOR UPDATE | AFTER INSERT ...]
AS EXTERNAL NAME Assembly.Triggers.Function_B
May not be ideal but it works.
kMaylo on Mon, 14 Jan 2008 18:18:15
Hi -
i have applied the generic code for trigger but i have an issue
lets say i have an application and an application user is login in it
and i use the generic code for Audit the DML on the tables
i want to Audit the userID of the application user from the generic code for trigger
thanx
Maylo
MikeBCA on Thu, 16 Jul 2009 22:13:22
One way is to add an Extended Property to the Table and query that property within the Trigger.
String
tableName = dataTable.ExtendedProperties["Name"].ToString();
This is a simple low tech solution and the only requirment is every table that uses this Trigger needs the extened property assigned.
Stefan Falz on Wed, 18 Nov 2009 23:31:39
Hi Mike,
One way is to add an Extended Property to the Table and query that property within the Trigger.this doesn't work for me (SQL 2005). Could you explain your solution a bit more?
tableName = dataTable.ExtendedProperties[ "Name" ].ToString();
This is a simple low tech solution and the only requirment is every table that uses this Trigger needs the extened property assigned.
I added the extended property to the original table and queried "inserted" and "deleted" from within the trigger. The returned DataTable doesn't contain any extened property.
rrozema on Wed, 24 Oct 2012 09:02:32
You could have the same core function that is called by a wrapper. Having a wrapper for each table and being attached the relevant table.No, you can't. The inserted and deleted pseudo tables are only accessible from a SQLCLR trigger, not from a SQLCLR procedure called from a T-SQL trigger.
rrozema on Wed, 24 Oct 2012 09:11:39
Chris,
have you actually tried to deploy the trigger m_shane_tx posted? As the trigger doesn't have a target, the deployment will fail (at least it does it for me). I.e AFAIK when you create a DML trigger, you have to have a target, so you can not hav a generic trigger for all tables.
Niels
All you need to do to create multiple triggers pointing to the same assembly is copy the create trigger statement generated by Visual studio, change the trigger name and the table name but not the assembly name and execute it. Et voila: you've got multiple triggers on multiple tables calling the same assembly.
The fact that it doesn't deploy for you is probably that since the original author wrote the article, the rules on the attributes have been tightened in Visual Studio. Just fill in a table name to get the assembly loaded and the create trigger statement generated for one table and do the rest of the tables manually. Or: comment the entire line and write all create trigger statements yourself.
rrozema on Wed, 24 Oct 2012 13:09:16
Apart from a few very far fetched work arounds there is no way to retrieve from a SQLCLR trigger the table for which it was fired. This is very strange as it is perfectly possible to create a single SQLCLR routine and create multiple triggers on several tables all calling that same assembly routine.
A very minor addition to the SqlTriggerCOntext object would fix all our issues. The addition would fit in very logically with the existing properties and methods in SQLTriggerContext. I have created a feature request om Microsofts feedback service Connect requesting to add a single property "ParentID" to SqlTriggerContext, where ParentID represents the object_id of the table that was modified.
Please help MS decide in favour of my request by voting on it (i.e. follow below link, sign in and click the up-arrow):
https://connect.microsoft.com/SQLServer/feedback/details/768358/a-sqlclr-trigger-should-be-given-the-parent-object-in-the-sqltriggercontext