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 the correct "Date" problem?

In a procedure that I am writing I have a select statement and need to say give me such and such results from the table where DateEntered is the first of the previous month??
Could somebody help me with that please?I can get you to the "first of the prior month", but you'll have to do the dirty deed from there on in. I'd use:SELECT DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01')-PatP|||Well that is great!!!!

This procedure will run a query once a month in the background,how can capture that data in a file and save it on my pc without doing it manually?|||Use OSQL.EXE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsrunui/dts_helprunui_6wq9.asp) to execute the stored procedure. You may want to start osql.exe from a job (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_automate_42r7.asp) in the SQL Agent.

-PatP|||This procedure will run a query once a month in the background,how can capture that data in a file and save it on my pc without doing it manually?

configure a job which runs monthly which does this for you.sql

getting scripts from sql server 2000

I'm interested in writing a program that makes craete scripts for all
views and stores procedures in a sql server 2000 database, like the
ones you can get them from the Query Analyzer.
Can I retrieve these create scripts using some SQL statement? Or do I
have to use some special MS SQL Server API?
Use SQL-DMO Objects Library
<delta.jentry@.gmail.com> wrote in message
news:1140506452.304401.85450@.g14g2000cwa.googlegro ups.com...
> I'm interested in writing a program that makes craete scripts for all
> views and stores procedures in a sql server 2000 database, like the
> ones you can get them from the Query Analyzer.
> Can I retrieve these create scripts using some SQL statement? Or do I
> have to use some special MS SQL Server API?
>
|||Hi
There are also scripting options in Enterprise Manager.
John
"delta.jentry@.gmail.com" wrote:

> I'm interested in writing a program that makes craete scripts for all
> views and stores procedures in a sql server 2000 database, like the
> ones you can get them from the Query Analyzer.
> Can I retrieve these create scripts using some SQL statement? Or do I
> have to use some special MS SQL Server API?
>

getting scripts from sql server 2000

I'm interested in writing a program that makes craete scripts for all
views and stores procedures in a sql server 2000 database, like the
ones you can get them from the Query Analyzer.
Can I retrieve these create scripts using some SQL statement? Or do I
have to use some special MS SQL Server API?Use SQL-DMO Objects Library
<delta.jentry@.gmail.com> wrote in message
news:1140506452.304401.85450@.g14g2000cwa.googlegroups.com...
> I'm interested in writing a program that makes craete scripts for all
> views and stores procedures in a sql server 2000 database, like the
> ones you can get them from the Query Analyzer.
> Can I retrieve these create scripts using some SQL statement? Or do I
> have to use some special MS SQL Server API?
>|||Hi
There are also scripting options in Enterprise Manager.
John
"delta.jentry@.gmail.com" wrote:
> I'm interested in writing a program that makes craete scripts for all
> views and stores procedures in a sql server 2000 database, like the
> ones you can get them from the Query Analyzer.
> Can I retrieve these create scripts using some SQL statement? Or do I
> have to use some special MS SQL Server API?
>

getting scripts from sql server 2000

I'm interested in writing a program that makes craete scripts for all
views and stores procedures in a sql server 2000 database, like the
ones you can get them from the Query Analyzer.
Can I retrieve these create scripts using some SQL statement? Or do I
have to use some special MS SQL Server API?Use SQL-DMO Objects Library
<delta.jentry@.gmail.com> wrote in message
news:1140506452.304401.85450@.g14g2000cwa.googlegroups.com...
> I'm interested in writing a program that makes craete scripts for all
> views and stores procedures in a sql server 2000 database, like the
> ones you can get them from the Query Analyzer.
> Can I retrieve these create scripts using some SQL statement? Or do I
> have to use some special MS SQL Server API?
>|||Hi
There are also scripting options in Enterprise Manager.
John
"delta.jentry@.gmail.com" wrote:

> I'm interested in writing a program that makes craete scripts for all
> views and stores procedures in a sql server 2000 database, like the
> ones you can get them from the Query Analyzer.
> Can I retrieve these create scripts using some SQL statement? Or do I
> have to use some special MS SQL Server API?
>sql

Getting proc name from within .Net code

Hi,

I'm currently in the midst of writing my first sqlclr sproc.

In our normal T-SQL sprocs we use the following:

OBJECT_NAME(@.@.PROCID)

to get the name of the sproc. We later use the result of this as a parameter to a custom message when we use RAISERROR.

Question is, is there a way of doing the same from within a SQLCLR sproc? i.e. Can someone give me a bit of code that will return the name of the current sproc? (Obviously this is not the same as the name of the .Net class that implements my sproc).

Thanks in advance for any help that you can provide.

Regards

I've had word from Umachander at MSFT who says that this isn't possible but that they are looking at it for a future version.

-Jamie

Getting null values while importing data from excel sheet to sqql server

Hai Friends,

Actually i want to import excel sheet in to sql server tables. I am writing the following query SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Book1.xls','SELECT * FROM [Sheet1$]') and able to retireve the data but the problem is the data present in the excel sheet is

Usernamepasswordhello1234512345hellohai-123haihaihaihellohello-123

Once i run this query i am getting it as

Username Password hello NULL
NULL hello
hai-123 haihai
haihello hello-123
can anybody please help me out in this case and how to solve this problem and if you have any piece of code please pass it on to me and i am in very urgent need waiting for your replies thanks in advance.

Friends, what i mean to say is i am getting null values in the results.

getting list of SQL Instances / Databases on network

I am using C#.NET and I am writing an application where I need to display to
the user in a comboBox all the SQL Server instances that can be detected and
dis. I have seen many applications like Enterprise Manager that can detect
them all. Once the user selects the instance, I would also like to get a lis
t
of all the databases stored into another combo.
How do I do this in C#?
Thanks for the help in advance.
David
Message posted via http://www.webservertalk.comhi
probably this can hekp you:
http://chanduas.blogspot.com/2005/0...-databases.html
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"David C via webservertalk.com" wrote:

> I am using C#.NET and I am writing an application where I need to display
to
> the user in a comboBox all the SQL Server instances that can be detected a
nd
> dis. I have seen many applications like Enterprise Manager that can detect
> them all. Once the user selects the instance, I would also like to get a l
ist
> of all the databases stored into another combo.
> How do I do this in C#?
> Thanks for the help in advance.
> David
>
> --
> Message posted via http://www.webservertalk.com
>|||This just lists the databases on a *known* single instance, which is also
treated here in more depth:
http://www.aspfaq.com/2456
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:3EAACA02-A627-4CE6-8A6E-E9CCFF6208D2@.microsoft.com...
> hi
> probably this can hekp you:
> http://chanduas.blogspot.com/2005/0...-databases.html
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "David C via webservertalk.com" wrote:
>|||If you are familiar with SQL-DMO, you can use ListAvailableServers()
You can also use the SQLPing utility; one version has C# source code
included.
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26
In addition, Gert has produced some tools:
http://www.sqldev.net/misc/EnumSQLSvr.htm
http://www.sqldev.net/misc/ListSQLSvr.htm
http://www.sqldev.net/misc/OleDbEnum.htm
"David C via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:52A41F2E6B25A@.webservertalk.com...
>I am using C#.NET and I am writing an application where I need to display
>to
> the user in a comboBox all the SQL Server instances that can be detected
> and
> dis. I have seen many applications like Enterprise Manager that can detect
> them all. Once the user selects the instance, I would also like to get a
> list
> of all the databases stored into another combo.
> How do I do this in C#?
> Thanks for the help in advance.
> David
>
> --
> Message posted via http://www.webservertalk.com|||Thank Chandra but that will only help me once I get the instance. I also nee
d
to know how to query all the instances that exist on the network. Any help
would be great from someone.
Thanks,
David
Chandra wrote:
>hi
>probably this can hekp you:
>http://chanduas.blogspot.com/2005/0...-databases.html
>
>[quoted text clipped - 7 lines]
Message posted via http://www.webservertalk.com|||Thank Chandra but that will only help me once I get the instance. I also nee
d
to know how to query all the instances that exist on the network. Any help
would be great from someone.
Thanks,
David
Chandra wrote:
>hi
>probably this can hekp you:
>http://chanduas.blogspot.com/2005/0...-databases.html
>
>[quoted text clipped - 7 lines]
Message posted via http://www.webservertalk.com|||Here's a very quick way to do it in C#
Create a new console application, and add this reference:
Project | Add Reference | COM | Microsoft SQLDMO Object Library
using System;
using System.Collections.Generic;
using System.Text;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
SQLDMO.Application sqlDmoApplication = new SQLDMO.Application();
SQLDMO.NameList serverList;
serverList = sqlDmoApplication.ListAvailableSQLServers();
foreach(string serverName in serverList)
{
Console.WriteLine(serverName);
}
}
}
}
That's it... now, as others might mention, this isn't 100% accurate, because
some servers in your network may be "hidden," and the service also has to be
started to be detected this way.
"David C via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:52A41F2E6B25A@.webservertalk.com...
>I am using C#.NET and I am writing an application where I need to display
>to
> the user in a comboBox all the SQL Server instances that can be detected
> and
> dis. I have seen many applications like Enterprise Manager that can detect
> them all. Once the user selects the instance, I would also like to get a
> list
> of all the databases stored into another combo.
> How do I do this in C#?
> Thanks for the help in advance.
> David
>
> --
> Message posted via http://www.webservertalk.com|||Aaron,
If I could give you 1000 points for giving the perfect answer I would.
This was an outstanding piece of code that worked perfectly. We have an SQL
expert here and he did not think of this. So props to you.
Thanks,
David
Aaron Bertrand [SQL Server MVP] wrote:
>Here's a very quick way to do it in C#
>Create a new console application, and add this reference:
>Project | Add Reference | COM | Microsoft SQLDMO Object Library
>using System;
>using System.Collections.Generic;
>using System.Text;
>namespace ConsoleApplication1
>{
> class Program
> {
> static void Main(string[] args)
> {
> SQLDMO.Application sqlDmoApplication = new SQLDMO.Application()
;
> SQLDMO.NameList serverList;
> serverList = sqlDmoApplication.ListAvailableSQLServers();
> foreach(string serverName in serverList)
> {
> Console.WriteLine(serverName);
> }
> }
> }
>}
>That's it... now, as others might mention, this isn't 100% accurate, becaus
e
>some servers in your network may be "hidden," and the service also has to b
e
>started to be detected this way.
>
>[quoted text clipped - 10 lines]
Message posted via http://www.webservertalk.com|||The best way to do it is using SQLBrowseConnect function from ODBC (no
SQLDMO dependency). Check the sample here:
http://www.codeproject.com/cs/database/LocatingSql.asp
Note: SQLBrowseConnect does not work if LAN is not available (cable
unplugged, etc.) while local instances are still accessible :-))
cheers,
</wqw>|||I too am looking for this same type of information. I read through the
response's and none gave me any information I could use. I've already tried
the one David said returned the information he needed. Here's what I am
looking for.
I've got SQL Server 2005 Express installed twice with 2 instances. The
first is the default SQLExpress and the second I named. We'll say
TestExpress. I know if I go into the registry under SQL Server it list both
of these instance names and I can probably retrieve this information from
there. But is there anyway to retrieve it via SQLDMO or any of the .NET SQL
references.
This code Aaron listed and David said it worked for him
SQLDMO.Application sqlDmoApplication = new SQLDMO.Application();
SQLDMO.NameList serverList;
serverList = sqlDmoApplication.ListAvailableSQLServers();
foreach(string serverName in serverList)
{
Console.WriteLine(serverName);
}
This only retrieved 2 items
{local}
the other was my computer name
Any help would be appreciated.
Joe
"David C via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:52A41F2E6B25A@.webservertalk.com...
>I am using C#.NET and I am writing an application where I need to display
>to
> the user in a comboBox all the SQL Server instances that can be detected
> and
> dis. I have seen many applications like Enterprise Manager that can detect
> them all. Once the user selects the instance, I would also like to get a
> list
> of all the databases stored into another combo.
> How do I do this in C#?
> Thanks for the help in advance.
> David
>
> --
> Message posted via http://www.webservertalk.com

Getting error while using "text" datatype

Hello, I am writing a sproc and am getting this error: Any ideas? Thanks!!

Msg 402, Level 16, State 1, Procedure InsertUserPreferences, Line 18

The data types text and text are incompatible in the equal to operator.

-

create procedure InsertUserPreferences

(

@.PublisherServer text

)

as

begin

if exists(Select Preference_StringList from USER_Preference where Preference_StringList = @.PublisherServer)

begin

--UPDATE

exec dbo.uProc_USER_Preference

end

If you are using 2005, change to using varchar(max) and this will work. You cannot use a text value in a equality comparison

create table test
(
textValue text
)
go
select *
from test
where textValue = ''
go

You could use a cast to only get the first 8000 characters:

select *
from test
where cast(textValue as varchar(8000)) = ''
go

If that will work for you.

Is that your entire proc? How des the update work? If it exists, do you just update the modify date?

|||I will give it a shot and get back with you. Thank you for your help!!!|||Dude that worked!! Thanks man!!!!

Getting error while using "text" datatype

Hello, I am writing a sproc and am getting this error: Any ideas? Thanks!!

Msg 402, Level 16, State 1, Procedure InsertUserPreferences, Line 18

The data types text and text are incompatible in the equal to operator.

-----------------------------------

create procedure InsertUserPreferences

(

@.PublisherServer text

)

as

begin

if exists(Select Preference_StringList from USER_Preference where Preference_StringList = @.PublisherServer)

begin

--UPDATE

exec dbo.uProc_USER_Preference

end

You cannot use an equal operator with TEXT or NTEXT. You can only use LIKE for comparison. Why are you using text, and not varchar? Are you using SQL 2000 or 2005?|||

Hello and thanks for responding!! I restored data from a SQL Server 2000 database to a SQL Server 2005 to meet our requirements. It was our DBA's decision to make that field a datatype of 'text'.

|||I would recommend changing it to VARCHAR if possible.