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 list of sessions

Hi,
I would like to get the list of connections/sessions from the server. Here
is the information that I wanted to retrieve.
Database Name
OS Username (from client)
Machine Name (client)
Type of Connection
Username (database)
Time of connection (used to filter output)
Is it possible to retrive using single SQL statement.
Thanks,
Ramutry following SP:
sp_who2
--
Ekrem Ã?nsoy
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:42ADB51C-4F38-4EBD-8E07-92557DBAA210@.microsoft.com...
> Hi,
> I would like to get the list of connections/sessions from the server. Here
> is the information that I wanted to retrieve.
> Database Name
> OS Username (from client)
> Machine Name (client)
> Type of Connection
> Username (database)
> Time of connection (used to filter output)
>
> Is it possible to retrive using single SQL statement.
> Thanks,
> Ramu|||Is it possible to issue select statement on sp_who2?
"Ekrem Ã?nsoy" wrote:
> try following SP:
> sp_who2
> --
> Ekrem Ã?nsoy
>
> "Ramu" <Ramu@.discussions.microsoft.com> wrote in message
> news:42ADB51C-4F38-4EBD-8E07-92557DBAA210@.microsoft.com...
> > Hi,
> >
> > I would like to get the list of connections/sessions from the server. Here
> > is the information that I wanted to retrieve.
> >
> > Database Name
> > OS Username (from client)
> > Machine Name (client)
> > Type of Connection
> > Username (database)
> > Time of connection (used to filter output)
> >
> >
> > Is it possible to retrive using single SQL statement.
> >
> > Thanks,
> > Ramu
>|||> Is it possible to issue select statement on sp_who2?
Not directly, but you can create a #temp table first, and insert into the
#temp table, then select from the #temp table.
http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html
Or, if you are using SQL Server 2005, you can create your own version of
sp_who2...
http://sqlserver2005.databases.aspfaq.com/better-sp-who2.html
http://sqlserver2005.databases.aspfaq.com/how-do-i-mimic-sp-who2.html|||Doesn't <SELECT * FROM master..sysprocesses> give you that info?
Linchi
"Ramu" wrote:
> Hi,
> I would like to get the list of connections/sessions from the server. Here
> is the information that I wanted to retrieve.
> Database Name
> OS Username (from client)
> Machine Name (client)
> Type of Connection
> Username (database)
> Time of connection (used to filter output)
>
> Is it possible to retrive using single SQL statement.
> Thanks,
> Ramu

Getting relationship data

Hi
I am trying to create a query or set of querys, which will allow me to retrieve the relationship data of a database. In sql Server 2000 the user can create diagrams which show these relationships, what i want to be able to do is get this data, but i am h
aving trouble finding a starting place. Could anyone point me in the right direction?
Thanks in advance
You can start by querying INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view.
If you are interested in viewing the reference constraints that exist in
your database, you can do:
SELECT o1.name AS "tablename",
OBJECT_NAME( f1.constid ) AS "constraintname",
OBJECT_NAME( f1.rkeyid ) as referencedtable
FROM sysobjects o1
LEFT OUTER JOIN sysconstraints c1
ON o1.id = c1.id
AND c1.status &3 = 3
LEFT OUTER JOIN sysforeignkeys f1
ON c1.constid = f1.constid
WHERE o1.type = 'u'
ORDER BY "referencedtable", "tablename" ;
Anith

Getting Records not in both tables

I have 2 tables and I want to retrieve all record that are in table 1, but
not in table 2. Here's my data.
Table 1: EstEquipment
Columns: EstEqpPK, EstimatePK, Description
Table 2: CrewEquipment
Columns: CrewEqpPK, EstEqpPK, quantity
I need all records in EstEquipment with a certain EstimatePK that are not in
CrewEquipment. I've tried all sorts of combinations of inner joins, outer
join, and not exists, but I can't seem to get it. This is my first attempt a
t
SQL, so bear with me.
This was my last attempt that got close:
SELECT EstEquipment.* FROM EstEquipment
LEFT OUTER JOIN CrewEquipment ON EstEquipment.EstEqpPK = CrewEquipment
.EstEqpPK
WHERE EstimatePK = '4947A70B4396448C'
Thanks!Try this one:
SELECT
e.EstEqpPK,
e.EstimatePK,
e.Description
FROM
EstEquipment e
LEFT OUTER JOIN
CrewEquipment c
ON
e.EstEqpPK = c.EstEqpPK
WHERE
e.EstimatePK = '4947A70B4396448C'
AND c.EstEqpPK IS NULL;
"Irvin McCoy" <IrvinMcCoy@.discussions.microsoft.com> wrote in message
news:CCD86D0D-60BC-4658-9E3A-4F9C49F63592@.microsoft.com...
>I have 2 tables and I want to retrieve all record that are in table 1, but
> not in table 2. Here's my data.
> Table 1: EstEquipment
> Columns: EstEqpPK, EstimatePK, Description
> Table 2: CrewEquipment
> Columns: CrewEqpPK, EstEqpPK, quantity
> I need all records in EstEquipment with a certain EstimatePK that are not
> in
> CrewEquipment. I've tried all sorts of combinations of inner joins, outer
> join, and not exists, but I can't seem to get it. This is my first attempt
> at
> SQL, so bear with me.
> This was my last attempt that got close:
> SELECT EstEquipment.* FROM EstEquipment
> LEFT OUTER JOIN CrewEquipment ON EstEquipment.EstEqpPK = CrewEquipment
> .EstEqpPK
> WHERE EstimatePK = '4947A70B4396448C'
> Thanks!|||Try
SELECT EstEquipment.* FROM EstEquipment
LEFT OUTER JOIN CrewEquipment ON EstEquipment.EstEqpPK =
CrewEquipment
.EstEqpPK
WHERE EstimatePK = '4947A70B4396448C'
AND CreqEquipment.EstEqpPK is NULL
HTH,
Stu|||Thanks, that got it. I had tried = NULL, but that didn't return anything. I'
d
better study up on the syntax.
"Stu" wrote:

> Try
> SELECT EstEquipment.* FROM EstEquipment
> LEFT OUTER JOIN CrewEquipment ON EstEquipment.EstEqpPK =
> CrewEquipment
> ..EstEqpPK
> WHERE EstimatePK = '4947A70B4396448C'
> AND CreqEquipment.EstEqpPK is NULL
> HTH,
> Stu
>|||Nothing will ever = NULL, since the definition of NULL is unknown.
Think about it this way, if I have a form with a field that says "gender"
and I forget to check either male or female, can you say with any certainty
that I am:
(a) male?
(b) female?
(c) not male?
(d) not female?
Further, can you say with any certainty that someone else, who also forgot
to specify their gender, is:
(a) the same gender as me?
(b) the opposite gender from me?
(c) not the same gender as me?
(d) not the opposite gender from me?
"Irvin McCoy" <IrvinMcCoy@.discussions.microsoft.com> wrote in message
news:E8475694-5E80-444B-BFDE-1F938DF76DBC@.microsoft.com...
> Thanks, that got it. I had tried = NULL, but that didn't return anything.
> I'd
> better study up on the syntax.|||I got that from my C background. You use to do this:
IF (x == null)
Thanks for the explanation.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Nothing will ever = NULL, since the definition of NULL is unknown.
> Think about it this way, if I have a form with a field that says "gender"
> and I forget to check either male or female, can you say with any certaint
y
> that I am:
> (a) male?
> (b) female?
> (c) not male?
> (d) not female?
> Further, can you say with any certainty that someone else, who also forgot
> to specify their gender, is:
> (a) the same gender as me?
> (b) the opposite gender from me?
> (c) not the same gender as me?
> (d) not the opposite gender from me?
>
>
> "Irvin McCoy" <IrvinMcCoy@.discussions.microsoft.com> wrote in message
> news:E8475694-5E80-444B-BFDE-1F938DF76DBC@.microsoft.com...
>
>

Getting records for latest 4 dates

Hi,
I would like to retrieve all the records in a table that are in the
latest 4 dates.
Each record has a date field. The latest date in the table may be today,
yesterday or last w - it varies.
I would like to be able to retrieve all records that belong to the
latest 4 dates in the table. Is it possible to do this in a stored
procedure?
Thanks,
DarenHi Daren
You may want to try something like:
CREATE TABLE #datedata ( dateval datetime, val varchar(10))
insert intO #datedata ( dateval, val )
select getdate(), 'a' as dateval
UNION ALL select getdate() -1, 'F'
UNION ALL select getdate() -2, 'b'
UNION ALL select getdate() -3, 'C'
UNION ALL select getdate() -4, 'E'
UNION ALL select getdate() -5, 'D'
SELECT * from #datedata
WHERE DATEVAL IN (
SELECT TOP 4 dateval FROM #datedata order by dateval DESC )
John
"Daren" <pearcy@.-removethis-gmail.com> wrote in message
news:42a2c8f6$0$303$cc9e4d1f@.news-text.dial.pipex.com...
> Hi,
> I would like to retrieve all the records in a table that are in the latest
> 4 dates.
> Each record has a date field. The latest date in the table may be today,
> yesterday or last w - it varies.
> I would like to be able to retrieve all records that belong to the latest
> 4 dates in the table. Is it possible to do this in a stored procedure?
> Thanks,
> Daren|||Hi John,
I don't think this does what I'm after. I'm not looking to get all
records with dates in the last 4 days. I'm looking to get records with
dates in the last 4 days in the table.
Looking at the example table below, the SQL I'm after would retrieve
record ids 3999 to 3991 inclusive.
RecordID Date Added
3999 25-May-2005
3998 25-May-2005
3997 23-May-2005
3996 21-May-2005
3995 21-May-2005
3994 21-May-2005
3993 21-May-2005
3993 21-May-2005
3992 18-May-2005
3991 18-May-2005
3990 16-May-2005
3989 16-May-2005
3988 15-May-2005
Regards,
Daren
John Bell wrote:
> Hi Daren
> You may want to try something like:
>
> CREATE TABLE #datedata ( dateval datetime, val varchar(10))
> insert intO #datedata ( dateval, val )
> select getdate(), 'a' as dateval
> UNION ALL select getdate() -1, 'F'
> UNION ALL select getdate() -2, 'b'
> UNION ALL select getdate() -3, 'C'
> UNION ALL select getdate() -4, 'E'
> UNION ALL select getdate() -5, 'D'
> SELECT * from #datedata
> WHERE DATEVAL IN (
> SELECT TOP 4 dateval FROM #datedata order by dateval DESC )

> John
> "Daren" <pearcy@.-removethis-gmail.com> wrote in message
> news:42a2c8f6$0$303$cc9e4d1f@.news-text.dial.pipex.com...
>
>
>|||This is why posting DDL and example data is important
(http://www.aspfaq.com/etiquett=ADe.asp?id=3D5006 ) because it removes
this sort of ambiguity.
CREATE TABLE MyWork ( RecordID int, [Date Added] datetime )
INSERT INTO MyWork ( RecordID, [Date Added] )
SELECT 3999, '25-May-2005'
UNION ALL SELECT 3998, '25-May-2005'
UNION ALL SELECT 3997, '23-May-2005'
UNION ALL SELECT 3996, '21-May-2005'
UNION ALL SELECT 3995, '21-May-2005'
UNION ALL SELECT 3994, '21-May-2005'
UNION ALL SELECT 3993, '21-May-2005'
UNION ALL SELECT 3993, '21-May-2005'
UNION ALL SELECT 3992, '18-May-2005'
UNION ALL SELECT 3991, '18-May-2005'
UNION ALL SELECT 3990, '16-May-2005'
UNION ALL SELECT 3989, '16-May-2005'
UNION ALL SELECT 3988, '15-May-2005'
SELECT * from MyWork
WHERE [Date Added] IN (
SELECT TOP 4 [Date Added] FROM ( SELECT DISTINCT [Date Added] FROM
MyWork ) A order by [Date Added] DESC )
This also seems to work as TOP is applied after building the result
set.
SELECT * from MyWork
WHERE [Date Added] IN (
SELECT DISTINCT TOP 4 [Date Added] FROM MyWork order by [Date Added]
DESC )=20
John|||John Bell wrote:
> This is why posting DDL and example data is important
> (http://www.aspfaq.com/etiquett_e.asp?id=5006 ) because it removes
> this sort of ambiguity.
>
Thanks for that advice, I'll try to remember it for future postings.

> This also seems to work as TOP is applied after building the result
> set.
> SELECT * from MyWork
> WHERE [Date Added] IN (
> SELECT DISTINCT TOP 4 [Date Added] FROM MyWork order by [Date Added]
> DESC )
>
D'oh! Why didn't I think of that? Thanks, much appreciated!
Daren|||SELECT * FROM tbl WHERE d in (SELECT TOP 4 d FROM tbl ORDER BY d DESC)
"Daren" wrote:

> Hi,
> I would like to retrieve all the records in a table that are in the
> latest 4 dates.
> Each record has a date field. The latest date in the table may be today,
> yesterday or last w - it varies.
> I would like to be able to retrieve all records that belong to the
> latest 4 dates in the table. Is it possible to do this in a stored
> procedure?
> Thanks,
> Daren
>|||OOPS, you might need:
SELECT * FROM tbl WHERE d in (SELECT TOP 4 d FROM (SELECT DISTINCT d FROM
tbl) a ORDER BY d DESC)
"Brian Selzer" wrote:
> SELECT * FROM tbl WHERE d in (SELECT TOP 4 d FROM tbl ORDER BY d DESC)
> "Daren" wrote:
>

Getting names from RID: db_id:file_id:page_no:row_no

Is there any way to retrieve table name and record data from RID:
db_id:file_id:page_no:row_no, for example,
RID: 1:1:1253:0 in my program?
Please reply me. Thanks in advance.
Regards,
Hyun-jik BaeYou may use the DBCC PAGE statement (undocumented). See:
http://www.sqlservercentral.com/col...ngdeadlocks.asp
http://www.microsoft.com/technet/pr...ks/inside6.mspx
Razvan

Getting my indexes

Hello all. I'm querying the SYSINDEXES table with the ID of mytable to
retrieve a list of all indexes for mytable. Under the "Name" column, I
notice several indexes that begin with "_WA" that aren't indexes I created;
I'm assuming these are SQL-internal indexes. Can someone explain what these
indexes are?
My ultimate goal is to populate a cursor with the names of my indexes and
then loop thru this cursor to perform a DBCC INDEXDEFRAG of these indexes.
This will eventually become a scheduled job that runs weekly. However, I
don't want to be defragmented useless indexes (or those that I haven't
intentionally built).
Any pointers, insights would be appreciated.
Thanks
RozRoz,
_WAxxxx are statistics over a non-indexed column and not indexes. They are
generated by Sql server.
hth
Quentin
"Roz" <Roz@.discussions.microsoft.com> wrote in message
news:6B733D2C-B1A7-400E-B5BF-E22F5875410F@.microsoft.com...
> Hello all. I'm querying the SYSINDEXES table with the ID of mytable to
> retrieve a list of all indexes for mytable. Under the "Name" column, I
> notice several indexes that begin with "_WA" that aren't indexes I
created;
> I'm assuming these are SQL-internal indexes. Can someone explain what
these
> indexes are?
> My ultimate goal is to populate a cursor with the names of my indexes and
> then loop thru this cursor to perform a DBCC INDEXDEFRAG of these indexes.
> This will eventually become a scheduled job that runs weekly. However, I
> don't want to be defragmented useless indexes (or those that I haven't
> intentionally built).
> Any pointers, insights would be appreciated.
> Thanks
> Roz

Getting last Identity value inserted in a table

Suppose we have a table with an identity field. Someone adds a record and wants to retrieve the value of the identity field from the just added record. I have seen code like this:

BEGIN TRAN

.... INSERT data

select newlyAddedID from table

END TRANS

In other words, they think that surrounding the INSERT and SELECT in a transaction insures they will get the correct value (even when other apps are updating this table).

Is this approach sound?

TIA,

barkingdog

Immediately after your insert, do:

SELECT SCOPE_IDENTITY()

Getting error, while trying to connect oracle

We are getting following error,while trying to connecct oracle using oledb provider.

Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from the propoerty will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page fromt he componnet's locale ID will be used.

Could you please tell me the reason for the error. is it problem regarding oracle client version installed on the system?

Thanks & Regards

S.Nagarajan

Are you sure its an error? Are you sure its not just a warning?

Did you try googling it? http://www.google.co.uk/search?hl=en&q=Cannot+retrieve+the+column+code+page+info+from+the+OLE+DB+Provider.+If+the+component+supports+the+%22DefaultCodePage%22+property%2C+the+code+page+from+that+property+will+be+used.+Change+the+value+of+the+property+if+the+current+string+code+page+values+are+incorrect.+If+the+component+does+not+support+the+property%2C+the+code+page+from+the+component%27s+locale+ID+will+be+used&meta=

This will definately help: http://blogs.conchango.com/jamiethomson/archive/2005/10/25/2303.aspx

-Jamie

|||

Thanks for your reply

Regards

S.Nagarajan