Getting the name of the updated table

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;

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.

|||

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

|||

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!

|||

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

|||

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

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

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 values

Loader.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_NAME

from 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 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 = AuditTable.NewRow();

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:

iRow = inserted.Rows[0];

PKString = PKStringBuilder(PKTable, iRow);

foreach (DataColumn column in inserted.Columns)

{

//Build an Audit Entry

aRow = AuditTable.NewRow();

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:

dRow = deleted.Rows[0];

PKString = PKStringBuilder(PKTable, dRow);

foreach (DataColumn column in inserted.Columns)

{

//Build and Audit Entry

aRow = AuditTable.NewRow();

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;

}

}

Hope this helps. Enjoy!!!!!!!

|||

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?

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

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?

|||

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

|||

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

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

Getting the name of the updated table

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;

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.

|||

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

|||

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!

|||

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

|||

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

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

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 values

Loader.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_NAME

from 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 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 = AuditTable.NewRow();

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:

iRow = inserted.Rows[0];

PKString = PKStringBuilder(PKTable, iRow);

foreach (DataColumn column in inserted.Columns)

{

//Build an Audit Entry

aRow = AuditTable.NewRow();

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:

dRow = deleted.Rows[0];

PKString = PKStringBuilder(PKTable, dRow);

foreach (DataColumn column in inserted.Columns)

{

//Build and Audit Entry

aRow = AuditTable.NewRow();

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;

}

}

Hope this helps. Enjoy!!!!!!!

|||

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?

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

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?

|||

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

|||

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

|||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
sql

Getting the name of the updated table

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;

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.

|||

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

|||

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!

|||

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

|||

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

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

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 values

Loader.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_NAME

from 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 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 = AuditTable.NewRow();

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:

iRow = inserted.Rows[0];

PKString = PKStringBuilder(PKTable, iRow);

foreach (DataColumn column in inserted.Columns)

{

//Build an Audit Entry

aRow = AuditTable.NewRow();

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:

dRow = deleted.Rows[0];

PKString = PKStringBuilder(PKTable, dRow);

foreach (DataColumn column in inserted.Columns)

{

//Build and Audit Entry

aRow = AuditTable.NewRow();

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;

}

}

Hope this helps. Enjoy!!!!!!!

|||

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?

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

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?

|||

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

|||

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

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

Getting the name of the updated table

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;

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.

|||

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

|||

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!

|||

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

|||

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

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

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 values

Loader.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_NAME

from 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 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 = AuditTable.NewRow();

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:

iRow = inserted.Rows[0];

PKString = PKStringBuilder(PKTable, iRow);

foreach (DataColumn column in inserted.Columns)

{

//Build an Audit Entry

aRow = AuditTable.NewRow();

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:

dRow = deleted.Rows[0];

PKString = PKStringBuilder(PKTable, dRow);

foreach (DataColumn column in inserted.Columns)

{

//Build and Audit Entry

aRow = AuditTable.NewRow();

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;

}

}

Hope this helps. Enjoy!!!!!!!

|||

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?

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

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?

|||

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

|||

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

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

Getting the name of the updated table

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;

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.

|||

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

|||

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!

|||

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

|||

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

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

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 values

Loader.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_NAME

from 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 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 = AuditTable.NewRow();

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:

iRow = inserted.Rows[0];

PKString = PKStringBuilder(PKTable, iRow);

foreach (DataColumn column in inserted.Columns)

{

//Build an Audit Entry

aRow = AuditTable.NewRow();

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:

dRow = deleted.Rows[0];

PKString = PKStringBuilder(PKTable, dRow);

foreach (DataColumn column in inserted.Columns)

{

//Build and Audit Entry

aRow = AuditTable.NewRow();

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;

}

}

Hope this helps. Enjoy!!!!!!!

|||

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?

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

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?

|||

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

|||

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

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

getting name of trigger in trigger itself

Hello,
i have a trigger on every table to check that only one row is to handle.
If there are more rows i want to show an error message with the
name of the trigger in which the error occured.
Now i have nearly 100 tables and also so much triggers which are
all identically from the statements inside, only the triggername for
the message is different. So is there a way to get the actual trigger
name instead of having the name hardcoded in each trigger?
tia,
HelmutWhy!!? It seems perverse to prevent multiple row updates. Especially
since the code to do this is probably less efficient than actually
performing the update.
Triggers have global scope and aren't easy to bypass on a live system,
so doing what you propose will make it extremely difficult to perform
any administrative maintenance on the data. Think very carefully about
the implications of this. Even if it is a requirement to limit users in
this way I strongly recommend you do so through SPs rather than
triggers. A trigger should always perform correctly for 0,1 or N rows
updated.
OBJECT_NAME(@.@.PROCID) will return the name of the executing trigger or
proc.
David Portas
SQL Server MVP
--

getting login id in trigger

I have a web application which connects to Microsoft SQL Server 2000 through
JDBC-ODBC Driver. The application server is JBoss and I am using connection
pooling.
When the application connects to the database it provides userid and
password which are 'sa' and 'password' respectively. They are constants for
all users. The user also type in his/her own login id which I stored in the
HTTPSession.
Problem is my triggers wants to get that login id. Is it possible?
Thanks
RizwanHi
You should not be using 'sa' as the login to SQL Server as this may be too
privileged.
The users login id is only used as authentication mechanism, therefore you
will either need to pass it as part of each call to the query/stored
procedures or possibly generate some kind of session token and pass that and
then use the session token as a link to the login.
John
"Rizwan" <hussains@.pendylum.com> wrote in message
news:5Ozce.14173$gA5.818174@.news20.bellglobal.com...
>I have a web application which connects to Microsoft SQL Server 2000
>through
> JDBC-ODBC Driver. The application server is JBoss and I am using
> connection
> pooling.
> When the application connects to the database it provides userid and
> password which are 'sa' and 'password' respectively. They are constants
> for
> all users. The user also type in his/her own login id which I stored in
> the
> HTTPSession.
> Problem is my triggers wants to get that login id. Is it possible?
>
> Thanks
> Rizwan
>|||> possibly generate some kind of session token and pass that and
> then use the session token as a link to the login.
can you explain this solution a bit more about what is session token?
thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uNfEUWWTFHA.3176@.TK2MSFTNGP09.phx.gbl...
> Hi
> You should not be using 'sa' as the login to SQL Server as this may be too
> privileged.
> The users login id is only used as authentication mechanism, therefore you
> will either need to pass it as part of each call to the query/stored
> procedures or possibly generate some kind of session token and pass that
and
> then use the session token as a link to the login.
> John
>
> "Rizwan" <hussains@.pendylum.com> wrote in message
> news:5Ozce.14173$gA5.818174@.news20.bellglobal.com...
>|||Hi
The easiest way is to store and pass the user_id that the person
authenticated with within you code. You than pass this value to each
procedure that is called e.g.
EXEC myProc @.user_id = 'John'
If you want to access the user_id in a trigger you would have to add a
user_id column to each table (say last_modified_by) and set 'John' as the
value. This way you can see who changed it by accessing the last_modified_by
in the inserted table in your trigger. Alternatively you can do the work
that the trigger would have done in the stored procedure and you would not
need the extra column.
A token would be a means of relating the session to the user, if you have a
users table it may be stored in there. That way you are not passing
something that is clearly a username, but you can get the username by
selecting the appropriate record from the users table.
HTH
John
"Rizwan" <hussains@.pendylum.com> wrote in message
news:9srde.1176$3U.240756@.news20.bellglobal.com...
> can you explain this solution a bit more about what is session token?
> thanks
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:uNfEUWWTFHA.3176@.TK2MSFTNGP09.phx.gbl...
> and
>

Getting latest record from trigger

Hello,

I have the following table (UNIQUOTE) with data in it:

Opportunity_IdRx_ComboPlan_Type
B6FG3JX5G$27/$48/$28HMO
B6FG3JX5G$88/$33/$99HMO
B6FG3JX5G$16/$17/$18HMO

There is a trigger on the table that updates another table (opport) on insert. The problem is that if I have an update to make to Rx_Combo it always grabs the first record no matter what. Any ideas on how to get it to update with the current/latest record data? I've looked into SCOPE_IDENTITY() but it can't get it to work. Any ideas? Here's the trigger:

CREATE TRIGGER [update_opportunity_hmo] ON [dbo].[UNIQUOTE]
FOR INSERT
AS
update opport set
opport.HMO_COMP_RX_COPAY=UNIQUOTE.rx_combo,
from opport, UNIQUOTE
where UNIQUOTE.opportunity_id=opport.id
and uniquote.plan_type='HMO'

Quote:

Originally Posted by mccax

Hello,

I have the following table (UNIQUOTE) with data in it:

Opportunity_IdRx_ComboPlan_Type
B6FG3JX5G$27/$48/$28HMO
B6FG3JX5G$88/$33/$99HMO
B6FG3JX5G$16/$17/$18HMO

There is a trigger on the table that updates another table (opport) on insert. The problem is that if I have an update to make to Rx_Combo it always grabs the first record no matter what. Any ideas on how to get it to update with the current/latest record data? I've looked into SCOPE_IDENTITY() but it can't get it to work. Any ideas? Here's the trigger:

CREATE TRIGGER [update_opportunity_hmo] ON [dbo].[UNIQUOTE]
FOR INSERT
AS
update opport set
opport.HMO_COMP_RX_COPAY=UNIQUOTE.rx_combo,
from opport, UNIQUOTE
where UNIQUOTE.opportunity_id=opport.id
and uniquote.plan_type='HMO'

try getting the value from inserted/deleted tables

Getting inserted data in trigger

I am using SQL Server 2000.

I want to create an after insert trigger on one of my tables, but I have forgotten how I reference the inserted data to do some business logic on it. Can someone please help.

Thanks

Jag

Hi!,

u can refer the iinserted row as "inserted":

select * from inserted OR inserted.field1 for a specific field.

Hope this will help.