Showing posts with label inserted. Show all posts

Thursday, March 29, 2012

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

Craig

SqlDataAdapter tableLoader = new SqlDataAdapter("SELECT * FROM inserted", connection);

DataTable insertedTable = new DataTable();

tableLoader.Fill(insertedTable);

string insertedTableName = insertedTable.TableName;

This is not a definitive statement just a word of warning.

|||

Thanks, again, for the response!

Craig

|||

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>

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,

Niels
|||

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?

|||

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

Trying using the TSQL they use to deploy the VB trigger to deploy my modified C# version.

mshanehorn

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

Craig

SqlDataAdapter tableLoader = new SqlDataAdapter("SELECT * FROM inserted", connection);

DataTable insertedTable = new DataTable();

tableLoader.Fill(insertedTable);

string insertedTableName = insertedTable.TableName;

This is not a definitive statement just a word of warning.

|||

Thanks, again, for the response!

Craig

|||

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>

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,

Niels
|||

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?

|||

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

Trying using the TSQL they use to deploy the VB trigger to deploy my modified C# version.

mshanehorn

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

Craig

SqlDataAdapter tableLoader = new SqlDataAdapter("SELECT * FROM inserted", connection);

DataTable insertedTable = new DataTable();

tableLoader.Fill(insertedTable);

string insertedTableName = insertedTable.TableName;

This is not a definitive statement just a word of warning.

|||

Thanks, again, for the response!

Craig

|||

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>

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,

Niels
|||

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?

|||

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

Trying using the TSQL they use to deploy the VB trigger to deploy my modified C# version.

mshanehorn

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

Craig

SqlDataAdapter tableLoader = new SqlDataAdapter("SELECT * FROM inserted", connection);

DataTable insertedTable = new DataTable();

tableLoader.Fill(insertedTable);

string insertedTableName = insertedTable.TableName;

This is not a definitive statement just a word of warning.

|||

Thanks, again, for the response!

Craig

|||

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>

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,

Niels
|||

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?

|||

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

Trying using the TSQL they use to deploy the VB trigger to deploy my modified C# version.

mshanehorn

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

Tuesday, March 27, 2012

Getting the Id of the row that i inserted with SQL 2005

Hi,

I use a Stored Procedure who works very well

...
INSERT INTO Computers (CategoryID, SubCategoryID, .....
VALUES (@.CategoryID, @.SubCategoryID, ....
...

But as soon as it creates the new row, i want to be able to get the Id (ComputerId) of this row. I use ComputerId as the primary key.

How can i do that? I code in VB.

Thanks

Add "RETURN SCOPE_IDENTITY()" to your stored procedure. Then you should be able to pick up this value as the stored procedure's return value.

|||

Thank you very much it works!

Getting the ID from an insert

Is there a way using t-sql to get the ID of an inserted row following the
insert?
Example:
@.RowID as int
@.RowID = INSERT INTO Contacts (Person,Phone_Number) Values('John
Smith','123-456-7890')
SELECT * FROM Contacts WHERE ID = @.RowID
Thanks,
ROnIf ID is an Identity column then you can use SCOPE_IDENTITY().
Andrew J. Kelly SQL MVP
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:OJoIDBu7FHA.1000@.tk2msftngp13.phx.gbl...
> Is there a way using t-sql to get the ID of an inserted row following the
> insert?
> Example:
> @.RowID as int
> @.RowID = INSERT INTO Contacts (Person,Phone_Number) Values('John
> Smith','123-456-7890')
>
> SELECT * FROM Contacts WHERE ID = @.RowID
>
>
> Thanks,
> ROn
>|||Till the days of SQL Server 7.0 we used to rely on @.@.IDENTITY function. But
in my experience that function isn't that reliable. i.e., @.@.IDENTITY isn't
dependent on the current scope. Even if we have inserted some records in a
different table it would fetch us that identity value ;) I am sure we
wouldn't be interested in that.
From SQL Server 2000 there is a new function by name SCOPE_IDENTITY which
returns the last IDENTITY value produced on a connection and by a statement
in the same scope. So its better to use SCOPE_IDENTITY in our select
statement to retrieve the identity value for the record which we inserted no
w.
Example: SELECT SCOPE_IDENTITY()
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"Andrew J. Kelly" wrote:

> If ID is an Identity column then you can use SCOPE_IDENTITY().
> --
> Andrew J. Kelly SQL MVP
>
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:OJoIDBu7FHA.1000@.tk2msftngp13.phx.gbl...
>
>|||So just to clarify another example might by
INSERT INTO PERSON ( SSN, BIRTHDATE)
VALUES( '123456789', '7/4/1976')
SELECT SCOPE_IDENTITY() AS PERSONID;
Is this correct?|||Contraptor@.gmail.com wrote:
> So just to clarify another example might by
> INSERT INTO PERSON ( SSN, BIRTHDATE)
> VALUES( '123456789', '7/4/1976')
> SELECT SCOPE_IDENTITY() AS PERSONID;
> Is this correct?
I would personally use an OUTPUT parameter, but the syntax you have is
correct.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Yes its correct!
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"Contraptor@.gmail.com" wrote:

> So just to clarify another example might by
> INSERT INTO PERSON ( SSN, BIRTHDATE)
> VALUES( '123456789', '7/4/1976')
> SELECT SCOPE_IDENTITY() AS PERSONID;
> Is this correct?
>sql

Monday, March 19, 2012

Getting Records Inserted in the Last(??)

Hi Everyone,
I am trying to write a stored proceedure, that will return records (from
2 Tables) where the last updated date was within the last hour.
Here is my query so far:
Select a.ID, UserName, JoinDate, LastAction as LastActivity, Title
From dbo.Access a
Join UserActions ua
on a.ID = ua.ID
Join News n
on a.Username = n.Poster
Where ua.LastAction < DateDiff(hour,GetDATE()-1, GETDATE())
If anyone could help, that would be great.
ThanksHello,
Try the below query:-
Select LastAction as LastActivity, Title
From dbo.Access a
Join UserActions ua
on a.ID = ua.ID
Join News n
on a.Username = n.Poster
Where Datediff(hh,getdate(),ua.LastAction)<=1
Thanks
Hari
"Mick Walker" <Mick.Walker@.privacy.net> wrote in message
news:5cg5qlF30ifdqU1@.mid.individual.net...
> Hi Everyone,
> I am trying to write a stored proceedure, that will return records (from 2
> Tables) where the last updated date was within the last hour.
> Here is my query so far:
> Select a.ID, UserName, JoinDate, LastAction as LastActivity, Title
> From dbo.Access a
> Join UserActions ua
> on a.ID = ua.ID
> Join News n
> on a.Username = n.Poster
> Where ua.LastAction < DateDiff(hour,GetDATE()-1, GETDATE())
> If anyone could help, that would be great.
> Thanks|||On 3 Jun, 16:42, Mick Walker <Mick.Wal...@.privacy.net> wrote:
> Hi Everyone,
> I am trying to write a stored proceedure, that will return records (from
> 2 Tables) where the last updated date was within the last hour.
> Here is my query so far:
> Select a.ID, UserName, JoinDate, LastAction as LastActivity, Title
> From dbo.Access a
> Join UserActions ua
> on a.ID = ua.ID
> Join News n
> on a.Username = n.Poster
> Where ua.LastAction < DateDiff(hour,GetDATE()-1, GETDATE())
> If anyone could help, that would be great.
> Thanks
...
WHERE ua.LastAction > DATEADD(HOUR,-1,CURRENT_TIMESTAMP);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Getting Records Inserted in the Last(??)

Hi Everyone,
I am trying to write a stored proceedure, that will return records (from
2 Tables) where the last updated date was within the last hour.
Here is my query so far:
Select a.ID, UserName, JoinDate, LastAction as LastActivity, Title
From dbo.Access a
Join UserActions ua
on a.ID = ua.ID
Join News n
on a.Username = n.Poster
Where ua.LastAction < DateDiff(hour,GetDATE()-1, GETDATE())
If anyone could help, that would be great.
ThanksHello,
Try the below query:-
Select LastAction as LastActivity, Title
From dbo.Access a
Join UserActions ua
on a.ID = ua.ID
Join News n
on a.Username = n.Poster
Where Datediff(hh,getdate(),ua.LastAction)<=1
Thanks
Hari
"Mick Walker" <Mick.Walker@.privacy.net> wrote in message
news:5cg5qlF30ifdqU1@.mid.individual.net...
> Hi Everyone,
> I am trying to write a stored proceedure, that will return records (from 2
> Tables) where the last updated date was within the last hour.
> Here is my query so far:
> Select a.ID, UserName, JoinDate, LastAction as LastActivity, Title
> From dbo.Access a
> Join UserActions ua
> on a.ID = ua.ID
> Join News n
> on a.Username = n.Poster
> Where ua.LastAction < DateDiff(hour,GetDATE()-1, GETDATE())
> If anyone could help, that would be great.
> Thanks|||On 3 Jun, 16:42, Mick Walker <Mick.Wal...@.privacy.net> wrote:
> Hi Everyone,
> I am trying to write a stored proceedure, that will return records (from
> 2 Tables) where the last updated date was within the last hour.
> Here is my query so far:
> Select a.ID, UserName, JoinDate, LastAction as LastActivity, Title
> From dbo.Access a
> Join UserActions ua
> on a.ID = ua.ID
> Join News n
> on a.Username = n.Poster
> Where ua.LastAction < DateDiff(hour,GetDATE()-1, GETDATE())
> If anyone could help, that would be great.
> Thanks
...
WHERE ua.LastAction > DATEADD(HOUR,-1,CURRENT_TIMESTAMP);
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Monday, March 12, 2012

Getting Previous Date SQL Script

Hello,

I have a table that also has a timestamp field I want to get any data that was inserted into that table on the previous day even if the month changes and i have no clue how to go about that

Tdar

Is your field actually a "timestamp" or a "datetime" field?|||

If your "timestamp" is actually a "datetime" field you can do:

select *
from yourTable
where dateTimeColumn < cast ( floor(cast(getdate() as float)) as datetime)
and dateTimeColumn >= cast ( floor(cast(getdate() as float)) - 1 as datetime)

or also

select *
from yourTable
where dateTimeColumn < convert(datetime, convert (varchar(10), getdate(), 101))
and dateTimeColumn >= convert(datetime, convert (varchar(10), getdate(), 101)) - 1

|||

SELECT * FROM yourDatesTable WHERE DATEDIFF(day, yourDateColumn, getdate())=1

|||Waldrop's is the better option here, because it doesn't need to apply a function to yourDateColumn before doing the comparison. This means that it can use indexes effectively.

Rob|||

limno wrote:
SELECT * FROM yourDatesTable WHERE DATEDIFF(day, yourDateColumn, getdate())=1

Another approach:
SELECT * FROM yourDatesTable WHERE yourDateColumn>DATEADD(day,-2,getdate()) AND yourDateColumn<=DATEADD(day,-1,getdate())

Wednesday, March 7, 2012

getting last record(s) inserted into a table

hi,
i want to get the rows last inserted into a table, there is no
identifier/auto incr column to determine? is it possible to do this? i think
physical order the records inserted may help, but how could we get the
records in the reverse physical order they inserted?
thanksif you don't have the primary key for the table then you can have a column
in the table with 'timestamp' datat type. Then you can enlist the last
record by
select top 1 * from mytable order by timestamp_column desc
"Philip" <philipfairheight_@.hotmail.com> wrote in message
news:ej1tNkLmDHA.2000@.TK2MSFTNGP12.phx.gbl...
> hi,
> i want to get the rows last inserted into a table, there is no
> identifier/auto incr column to determine? is it possible to do this? i
think
> physical order the records inserted may help, but how could we get the
> records in the reverse physical order they inserted?
> thanks
>|||Actually, the previous suggestion will not work if you are doing any
updating... See example
drop table test
go
create table test (id int identity(1,1), ti timestamp, a varchar(100) not
null )
go
insert into test(a) values ('hi')
insert into test(a) values ('hi')
insert into test(a) values ('hi')
insert into test(a) values ('hi')
insert into test(a) values ('hi')
insert into test(a) values ('hi')
go
select * from test
go
update test set a = 'lo' where id = 3
go
select * from test
You'll notice that the timestamp column value for id = 3 is the largest...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Philip" <philipfairheight_@.hotmail.com> wrote in message
news:ej1tNkLmDHA.2000@.TK2MSFTNGP12.phx.gbl...
> hi,
> i want to get the rows last inserted into a table, there is no
> identifier/auto incr column to determine? is it possible to do this? i
think
> physical order the records inserted may help, but how could we get the
> records in the reverse physical order they inserted?
> thanks
>|||u r right . because the timestamp value is updated automatically whenever
the record is inserted or updated.
poor solution for poor table design. (how come there be a table without
identifer (PK) ?)
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:ugZ%23$fMmDHA.372@.TK2MSFTNGP11.phx.gbl...
> Actually, the previous suggestion will not work if you are doing any
> updating... See example
> drop table test
> go
> create table test (id int identity(1,1), ti timestamp, a varchar(100) not
> null )
> go
> insert into test(a) values ('hi')
> insert into test(a) values ('hi')
> insert into test(a) values ('hi')
> insert into test(a) values ('hi')
> insert into test(a) values ('hi')
> insert into test(a) values ('hi')
> go
> select * from test
> go
> update test set a = 'lo' where id = 3
> go
> select * from test
> You'll notice that the timestamp column value for id = 3 is the largest...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Philip" <philipfairheight_@.hotmail.com> wrote in message
> news:ej1tNkLmDHA.2000@.TK2MSFTNGP12.phx.gbl...
> > hi,
> > i want to get the rows last inserted into a table, there is no
> > identifier/auto incr column to determine? is it possible to do this? i
> think
> > physical order the records inserted may help, but how could we get the
> > records in the reverse physical order they inserted?
> > thanks
> >
> >
>|||You also have the option of including and update trigger
on the table to update a date/time field to the time of
the update. These operations can start to get a bit
expensive depending on how many updates you are doing. If
you are doing primarily inserts a default will work fine.
I do not know of any way to get this information with the
current internal storage structures.
Skippy|||anyway, shall we depend on the structure of the data, i mean sql server
returns always the records in the order they are inserted or this order
changes when pages optimized/compressed?
"I_AM_DON_AND_YOU?" <user@.domain.com> wrote in message
news:eqMtgmMmDHA.3688@.TK2MSFTNGP11.phx.gbl...
> u r right . because the timestamp value is updated automatically whenever
> the record is inserted or updated.
> poor solution for poor table design. (how come there be a table without
> identifer (PK) ?)
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:ugZ%23$fMmDHA.372@.TK2MSFTNGP11.phx.gbl...
> > Actually, the previous suggestion will not work if you are doing any
> > updating... See example
> >
> > drop table test
> > go
> > create table test (id int identity(1,1), ti timestamp, a varchar(100)
not
> > null )
> > go
> > insert into test(a) values ('hi')
> > insert into test(a) values ('hi')
> > insert into test(a) values ('hi')
> > insert into test(a) values ('hi')
> > insert into test(a) values ('hi')
> > insert into test(a) values ('hi')
> > go
> > select * from test
> > go
> > update test set a = 'lo' where id = 3
> > go
> > select * from test
> >
> > You'll notice that the timestamp column value for id = 3 is the
largest...
> >
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Computer Education Services Corporation (CESC), Charlotte, NC
> > www.computeredservices.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> >
> > "Philip" <philipfairheight_@.hotmail.com> wrote in message
> > news:ej1tNkLmDHA.2000@.TK2MSFTNGP12.phx.gbl...
> > > hi,
> > > i want to get the rows last inserted into a table, there is no
> > > identifier/auto incr column to determine? is it possible to do this? i
> > think
> > > physical order the records inserted may help, but how could we get the
> > > records in the reverse physical order they inserted?
> > > thanks
> > >
> > >
> >
> >
>|||> anyway, shall we depend on the structure of the data
No. The optimizer is free to process the query in any way it wants. You cannot rely on anything
(indexes, order of inserts etc). If you don't have ORDER BY, you get the rows in the order the
optimizer will find most efficient.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Philip" <philipfairheight_@.hotmail.com> wrote in message
news:eOlOPaSmDHA.744@.tk2msftngp13.phx.gbl...
> anyway, shall we depend on the structure of the data, i mean sql server
> returns always the records in the order they are inserted or this order
> changes when pages optimized/compressed?
> "I_AM_DON_AND_YOU?" <user@.domain.com> wrote in message
> news:eqMtgmMmDHA.3688@.TK2MSFTNGP11.phx.gbl...
> > u r right . because the timestamp value is updated automatically whenever
> > the record is inserted or updated.
> >
> > poor solution for poor table design. (how come there be a table without
> > identifer (PK) ?)
> >
> > "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> > news:ugZ%23$fMmDHA.372@.TK2MSFTNGP11.phx.gbl...
> > > Actually, the previous suggestion will not work if you are doing any
> > > updating... See example
> > >
> > > drop table test
> > > go
> > > create table test (id int identity(1,1), ti timestamp, a varchar(100)
> not
> > > null )
> > > go
> > > insert into test(a) values ('hi')
> > > insert into test(a) values ('hi')
> > > insert into test(a) values ('hi')
> > > insert into test(a) values ('hi')
> > > insert into test(a) values ('hi')
> > > insert into test(a) values ('hi')
> > > go
> > > select * from test
> > > go
> > > update test set a = 'lo' where id = 3
> > > go
> > > select * from test
> > >
> > > You'll notice that the timestamp column value for id = 3 is the
> largest...
> > >
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Computer Education Services Corporation (CESC), Charlotte, NC
> > > www.computeredservices.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > >
> > > "Philip" <philipfairheight_@.hotmail.com> wrote in message
> > > news:ej1tNkLmDHA.2000@.TK2MSFTNGP12.phx.gbl...
> > > > hi,
> > > > i want to get the rows last inserted into a table, there is no
> > > > identifier/auto incr column to determine? is it possible to do this? i
> > > think
> > > > physical order the records inserted may help, but how could we get the
> > > > records in the reverse physical order they inserted?
> > > > thanks
> > > >
> > > >
> > >
> > >
> >
> >
>

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 last ID MSSQLServer

Hi netpeople.
I have got a problem with setting parameters to a statement. I would
like get the last inserted id of a table.
The SQL statement for this on mssqlserver is:
SELECT IDENT_CURRENT(<tablename>) AS <idfieldname>
I use a PrepareStatement:
SELECT IDENT_CURRENT(?) AS ?
How do I set this parameter to the tablename and the idfieldname?
I tried with:
PreparedStatement oStmt = null;
oStmt =
m_oConn.prepareStatement(JDBCStmt.CBP_RETURNINTERF ACE_UPDATE_CONTAINER);
oStmt.setString(1, sTable);
oStmt.setString(2, sField);
But with this implementation, the executeQuery() fails.
Special Thanks for your help.
Oliver Hirschi
http://www.FamilyHirschi.ch
Oliver Hirschi wrote:

> Hi netpeople.
> I have got a problem with setting parameters to a statement. I would
> like get the last inserted id of a table.
> The SQL statement for this on mssqlserver is:
> SELECT IDENT_CURRENT(<tablename>) AS <idfieldname>
> I use a PrepareStatement:
> SELECT IDENT_CURRENT(?) AS ?
> How do I set this parameter to the tablename and the idfieldname?
> I tried with:
> PreparedStatement oStmt = null;
> oStmt =
> m_oConn.prepareStatement(JDBCStmt.CBP_RETURNINTERF ACE_UPDATE_CONTAINER);
> oStmt.setString(1, sTable);
> oStmt.setString(2, sField);
> ----
> But with this implementation, the executeQuery() fails.
> Special Thanks for your help.
Hi. It can't work that way. Parametners are only really for *data* values,
that could be plugged into a precompiled query plan. If you try to have
the table name as a parameter, it completely alters what would be a plan.
Just do this:
Statement s = oConn.createStatement();
ResultSet rs = s.executeQuery("SELECT IDENT_CURRENT(" + sTable + ") AS " + sField );
Joe Weinstein at BEA

getting key just entered

************* Edited by moderator Adec ***************
Inserted missing < code></ code> tags (without the
spaces inside). Always include such tags when
including code in your postings. Don't force the
moderators to do this for you. Many readers disregard
postings without the code tags.
**************************************************

Probably a dumb question but I'm doing an insert into a table with an identity field. How do I get the key back straight after I add it?

This is how I do the addition


SQLString = "Insert into users (username,password,status,campus, ulevel ) values ( @.username, @.password, @.status, @.campus, @.ulevel)"
 cmdInsert = new SQLCommand(SQLString, conn)
 cmdInsert.Parameters.Add( "@.username", _username)
 cmdInsert.Parameters.Add( "@.password", md5Hasher.ComputeHash(encoder.GetBytes(_password)))
 cmdInsert.Parameters.Add( "@.status", _status)
 cmdInsert.Parameters.Add( "@.campus", _campus)
 cmdInsert.Parameters.Add( "@.ulevel", _ulevel )
 conn.Open()
 cmdInsert.ExecuteNonQuery()
 conn.close()

but about now I need the primary key just generated.
Any ideas?

Thanks
ABoldWhat database do you use.|||If you are using SQL server, you can return the value of @.@.identity after the insert.|||I wouldnt use @.@.identity. When you are insert into a table that has a trigger and creates another identity value then you will get the last value generated in any table. Use instead SCOPE_IDENTITY(). It will return the generated value for the current scope.

Morehere...|||Like I said, first we need to know which Db the Poster is using. Then we can decide which way to go. SCOPE_IDENTITY() is only available in SQL 2000 Server and later.|||Yes I'm using SQL Server 2000.

SO I can use SCOPE_IDENTITY() thanks I'll look it up.

Thanks
ABold

Sunday, February 26, 2012

Getting inserted data in trigger

I am using SQL Server 2000.

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

Thanks

Jag

Hi!,

u can refer the iinserted row as "inserted":

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

Hope this will help.

Getting Identity/Serial of Row Just Inserted?

This isn't so much purely a SQL Server question as a question on ASP.NET VB technique. In particular, I have a situation where I am either inserting a NEW row for a "profile table" (name, email, etc.) or Updating an existing one. In both cases, I need to create a new row in a related table which has the identity/serial column of the parent table as the primary key for the data to be inserted into this subsidiary table (for which there may be many rows inserted, all tying back to the parent).

At the time I do the update, of course, I have the identity/serial of the "parent" so it's easy to update/insert. However, if the profile is NEW, I need to capture the identity/serial which was inserted so as to use it for the child table insert. (I remember a call to an obscure function which was -- essentially -- "give me the identity/serial of that which was just INSERTed" but I am unable to locate equivalent functionality. (I have searched various online help files for "Insert serial", "Insert identity" and the like with no results.

Hints? Mahalos in advance ... :) KevInKauai

You can use the SCOPE_IDENTITY() function to retrieve the ID of the row just inserted. Check out books on line to read up some info on the function.

|||

@.@.IDENTITY

Returns the last-inserted identity value.

Syntax

@.@.IDENTITY

Return Types

numeric

Remarks

After an INSERT, SELECT INTO, or bulk copy statement completes, @.@.IDENTITY contains the last identity value generated by the statement. If the statement did not affect any tables with identity columns, @.@.IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @.@.IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @.@.IDENTITY immediately after the statement returns the last identity value generated by the triggers. The @.@.IDENTITYvalue does not revert to a previous setting if the INSERT or SELECT INTO statementor bulk copy fails, or if the transaction is rolled back.

@.@.IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.

@.@.IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @.@.IDENTITY is not limited to a specific scope.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, seeIDENT_CURRENT.

Examples

This example inserts a row into a table with an identity column and uses @.@.IDENTITY to display the identity value used in the new row.

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @.@.IDENTITY AS 'Identity'

|||

Perhaps I wasn't clear. I'm doing this in an ASP.NET application (..aspx) -- NOT in Transact-SQL -- where neither IDENTITY_CURRENT nor any of those other TRANSACT-SQL constants seems to be available.

KevInKauai

|||

KevInKauai:
NOT in Transact-SQL

Then perhaps you should convert it to a stored proc and youre life will be easier Big Smile

|||

KevInKauai:
Perhaps I wasn't clear. I'm doing this in an ASP.NET application (..aspx) -- NOT in Transact-SQL -- where neither IDENTITY_CURRENT nor any of those other TRANSACT-SQL constants seems to be available.
KevInKauai

But it is still what you need to do. How exactly you implement it depends on how you're doing the code, but SCOPE_IDENTITY is what you need. (Mehedi: @.@.IDENTITY is generally not recommended, since concurrent operations can return the wrong value. SCOPE_IDENTITY() virtually always returns what you actually need.)

Are you running dynamic SQL in your ASP.NET application? Then you can tack on a call to SCOPE_IDENTITY() to the end of the query. Something like this:

string sql = "INSERT <row into primary table>; SELECT SCOPE_IDENTITY()"

Then execute the code and read the return value from the statement.

As David commented, this is all much easier and cleaner if you use a stored procedure, because then the new identity can either be returned as a single row, single column record set, or as the return value from the procedure.

In essence, you need to cause SQL Server to send you the value, which requires SCOPE_IDENTITY. But there are various ways to get it to do that.

Make sense?

Don

|||

Hi, Don - -

I prefer not to deal with Stored Procedures in general as they tend (to me) to be cumbersome and require extra steps rather than the "on-the-fly" development that I am presently dealing with.

That said, apprenting the "SCOPE_IDENTITY" to the INSERT seemed to not get an error, but where does the result come back?

1 SQL =String.Format("INSERT INTO [Parent] ([parentData]) " & _2 "VALUES ('{0}'); SELECT SCOPE_IDENTITY() ", _3 txtRowData.Text)45 SqlDataSource1.InsertCommand = SQL67Try8 SqlDataSource1.Insert()

The row got inserted (verified that), but now how do I get that identity? (Sorry to be such a blank here. This serial stuff was always obscure and I guess we can blame Chris Date for not including it more formally in the SQL definitiong.)

tia ... :) KevInKauai

Thursday, March 29, 2012

Getting the name of the updated table

Getting the name of the updated table

Getting the name of the updated table

Getting the name of the updated table

Getting the name of the updated table

Tuesday, March 27, 2012

Getting the Id of the row that i inserted with SQL 2005

Getting the ID from an insert

Monday, March 19, 2012

Getting Records Inserted in the Last(??)

Getting Records Inserted in the Last(??)

Monday, March 12, 2012

Getting Previous Date SQL Script

Wednesday, March 7, 2012

getting last record(s) inserted into a table

Getting last Identity value inserted in a table

getting last ID MSSQLServer

getting key just entered

Sunday, February 26, 2012

Getting inserted data in trigger

Getting Identity/Serial of Row Just Inserted?

@.@.IDENTITY

Syntax

Return Types

Remarks

Examples

File Group Changes

Blog Archive

About Me