Not as easy as it sounds. If you are passing in statements like:
Update Orders Set TotalTax = 1000 Where ShippingState = 'TX'
And then you want to know what rows you changed... that's definitely not
simple.
A couple of ideas:
1) Triggers: this is probably the most common. Triggers in SQL Server get a
rowset that contains the changed rows. You can add a new column to your
table, and then you can add code into an Insert or Update trigger to change
the value of that column to indicate a particular value... then you can
readily look for the list of rows with that value. (Getting that value into
the trigger is hard, but this requires few changes to your existing
queries).
2) Similar to #1 above, but do this in the SQL stmt itself... Add a
transactionId column to your table, then change your SQL queries to
something like this:
Guid TransId = Guid.NewGuid();
String sSql;
sSql = "Update Orders Set TotalTax = 1000, TransactionId = " +
TransId.ToString() + " Where ShippingState = 'TX'";
(note: I prefer stored procs... I used a SQL stmt to demonstrate the
concept, not to encourage this practice. no flames please).
3) Prequeries:
In a stored procedure, open a transaction. Then query the database to
return the primary key of every row that matches your update query. Then
perform the update and commit. In the application code, capture the list of
primary keys. You now have a rowset that you can use to determine what rows
were updated.
4) Transaction tables:
Create a transaction table of the sort:
TransactionId (PK) (either UniqueIdentifier or int Identity(1,1))
RowPrimaryKey (FK)
SelectDate (datetime)
Then, when you want to update rows in the table of interest, call a
procedure that adds rows to your transaction table for the primary key of
each row that matches your particular criteria:
Insert TransTable (TransactionId, RowPrimaryKey, SelectDate) Select
@TransId, OrderId, getdate() from Orders where ShippingState = 'TX'
As you can see, I prefer Guids over Identity columns... I've spent way too
much time trying to fix a poor design when doing a merge.
If, on the other hand, you use Identity columns, the db can create the
value for you, but you will have to have the sp return the @@Identity
variable.
Anyway, the middleware now has a transaction id. Use that to update your
rows.
Update Orders Set TotalTax = 1000 where OrderId in (Select RowPrimaryKey
from TransTable Where TransactionId = @TransId)
This one allows you to create a set of rows that met the criteria at a
particular point in time, and maintain that set over time, regardless of how
the rows themselves change. You can perform many actions on the set, and
use it for auditing and reporting, both before and after you apply the
actual change.
I don't know which of these techniques you may wish to use... Just some
suggestions.
--- Nick
"Support" <an*******@discussions.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
Hi,
I want to know if I have changed a few records in my database using update
/ insert / delete methods, how can i later know which rows have been changed
or modified ?
I know the ExecuteNonQuery method which can give me the "number" of
modified rows, but which rows are changed how do I know ?
Any ideas ?
