I want to be able to find the differences between the before and after
values in a table as updates occur. I thought an easy way to do this
would be to create another table with an identical structure and then
use an update trigger to insert the deleted and inserted rows into
that alternate table. I know what order the rows are in the table
since I will put them in there but how can I, without a time value

email notifications aren't necessarily terribly reliable.
I find it advisable to have a screen ( as well ) where you can see
notifications.

If you want to be sure of the order then I suggest writing to a log
file would be better than a table.
The order that data is in will not be useful otherwise.
I would recommend creating a table which has a bunch of fields for
before and the same again for after.
Plus your primary (unique ) key, a datestamp and change indicator (
Insert, Update, Delete ).
Write this with your trigger.

What I'd do with it then depends on how dynamic the data is.
I would hope that it's not very dynamic of all this is almost certainly
a complete waste of time.
Anyhow.
Stick a screen on the front of your app that the administrator only
sees with the changes from yesterday and today presented on it.
Use the timestamp to drive the selection.

On Wed, 04 Jan 2006 17:35:05 -0600, Trey Walpole
[quoted text, click to view]
I know that selection usually includes an "order by" clause, but the
data must be in the db in some order.

[quoted text, click to view]
In this instance, I don't care when the data was changed, only that it
was. A web application is supposed to send an email to an
administrator showing db modifications. Having a "before" row and an
"after" row would make this easy.

[quoted text, click to view]
If I knew the order I would know which row it was because I will

[quoted text, click to view]
It's in the database in some order, true. But there is no guarantee of
the order in which the server will retrieve rows, unless you impose an
ordering. It is *entirely* up to the server in what order it returns a
set of rows, and the order you receive them in may depend on server
version, patches, number of processors, *workload*, *data volumes*,
*indexes* and *statistics*. (the * ones are ones likely to change just
in the day-to-day use of a database). So if you need to retrieve data
in an order based on when it was inserted, you best record that
information.

In general, for small tables, your data will be returned to you in the
order determined by the clustered index (if it exists), or the order in
which data was inserted (if no clustered index). However, this is for
very small tables (I think as soon as you start using two pages, the
server can start reordering the rows as it sees fit, but not sure)

Damien

[quoted text, click to view]

no, it's not. it's wherever the dbms put it. it could be in order, it
might not be, even for clustered indexes.
there is no intrinsic row number or insertion order. if you want one,
you have to add one.


[quoted text, click to view]

so what's the problem with adding a column that will help you?
"i don't care when the data was changed...." - famous last words :)

[quoted text, click to view]

if you really do not care and can honestly say that you will never care
when the data was changed, then you could add an identity column to your
auditing table.

your better bet would be a single row with before and after values for