Getting the name of the updated table

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

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();

//Get the deleted and/or overwritten values

Loader.SelectCommand.CommandText = "SELECT * from DELETED";

DataTable deleted = new DataTable();

//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);

//Retrieve the UserName of the current Database User

SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn);

//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_NAME

where pk.TABLE_NAME = '" + TName + @"'

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn);

DataTable PKTable = new DataTable();

switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table

case TriggerAction.Update:

iRow = inserted.Rows[0];//Get the inserted values in row form

dRow = deleted.Rows[0];//Get the overwritten values in row form

PKString = 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 Entry

aRow["ActionType"] = "U";//U for Update

aRow["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:

foreach (DataColumn column in inserted.Columns)

//Build an Audit Entry

aRow["ActionType"] = "I";//I for Insert

aRow["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:

foreach (DataColumn column in inserted.Columns)

//Build and Audit Entry

aRow["ActionType"] = "D";//D for Delete

aRow["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 AuditTable

conn.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;

ckimmel on Wed, 15 Aug 2007 21:53:40

Man!  I really thought you had it with:

nielsb on Thu, 16 Aug 2007 09:19:57

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

Alphi on Thu, 04 Oct 2007 07:54:03

];
GO
GRANT SELECT ON OBJECT:ys.dm_tran_locks TO [

GO
GRANT EXECUTE ON OBJECT:p_lock TO [

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

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

One way is to add an Extended Property to the Table and query that property within the Trigger.

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.

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.

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