Can I determine insert order without an explicit field

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
column, know which one was inserted into the table first?You can't. It would be very easy to add a column, e.g., InsertDate, with a
default of CURRENT_TIMESTAMP. In your alternate table you wouldn't need a
default on it.
HTH
Vern Rabe
"Computer User" wrote:

> 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
> column, know which one was inserted into the table first?
>|||ordering is an aspect of data selection, so you need some sort of
ordering column to indicate time based data.
there's no such thing intrinsically in a sql table as a row number, so
you really don't know what order the rows are in the table.
why wouldn't you want a datetime stamp column?
if you care that data was changed, wouldn't you want to know when it
changed?
you'll probably also want an indicator for which row it was
[deleted/inserted]
Computer User wrote:
> 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
> column, know which one was inserted into the table first?|||On Wed, 04 Jan 2006 17:35:05 -0600, Trey Walpole
<treypole@.newsgroups.nospam> wrote:

>ordering is an aspect of data selection, so you need some sort of
>ordering column to indicate time based data.
>there's no such thing intrinsically in a sql table as a row number, so
>you really don't know what order the rows are in the table.
>
I know that selection usually includes an "order by" clause, but the
data must be in the db in some order.

>why wouldn't you want a datetime stamp column?
>if you care that data was changed, wouldn't you want to know when it
>changed?
>
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.

>you'll probably also want an indicator for which row it was
>[deleted/inserted]
>
If I knew the order I would know which row it was because I will
insert the deleted row before the inserted row.|||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.|||Computer User wrote:
> On Wed, 04 Jan 2006 17:35:05 -0600, Trey Walpole
> <treypole@.newsgroups.nospam> wrote:
>
> I know that selection usually includes an "order by" clause, but the
> data must be in the db in some order.
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|||Computer User wrote:
> On Wed, 04 Jan 2006 17:35:05 -0600, Trey Walpole
> <treypole@.newsgroups.nospam> wrote:
>
> I know that selection usually includes an "order by" clause, but the
> data must be in the db in some order.
>
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.

> 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.
>
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 :)

> If I knew the order I would know which row it was because I will
> insert the deleted row before the inserted row.
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
each column being audited.

Can errors occur while committing a tx?

Can an error occur /during/ the process of committing a transaction?
BeginTraction();
try {
UpdateTable1();
UpdateTable2();
CommitTransaction(); <-- error here?
} catch(Exception e){
RollbackTransaction();
}
Also, what are the implications in such a situation where both the
Updates pass without any errors, but committing the transaction fails?
Is this scenario even possible at all?
If so, what are the suggested best-practices for recovering from such
types of errors?
TIA,
Abdullah"Abdullah Kauchali" <none@.none.com> wrote in message
news:eT1t1UAzFHA.1264@.tk2msftngp13.phx.gbl...
> Can an error occur /during/ the process of committing a transaction?
> BeginTraction();
> try {
> UpdateTable1();
> UpdateTable2();
> CommitTransaction(); <-- error here?
> } catch(Exception e){
> RollbackTransaction();
throw;
> }
>
Remember to rethrow the exception!
You know, that's a really good question.
Yes errors are possible, but pretty darn unlikely (at least in SQL Server).
When you go to commit the transaction all of the changes have allready been
made to the tables, and written to the memory cache of the log file. So
almost everything that could go wrong already would have. There could
possibly be some error flushing the log to disk, or you could loose your
connection to the database server, or the server could just fail. If the
commit fails on the server, the transaction will be rolled back (or at worst
it won't be there when the database recovers). But from a client there's
probably some possiblility that the commit succeeds, but a network problem
prevents you from learning about it.

> Also, what are the implications in such a situation where both the
> Updates pass without any errors, but committing the transaction fails?
The transaction will be rolled back.

> Is this scenario even possible at all?
> If so, what are the suggested best-practices for recovering from such
> types of errors?
>
Treat it like a server or network failure.
It's so unlikely to happen in the first place, and you are so unlikely to be
able to recover if it does, that I would just pretend like it's impossible.
Just pretend like it can't happen. Your responsibility is only to keep the
database in a logically consistent state. You've done that by coding your
transaction. You are not responsible for making sure the transaction
suceeds. That responsibility belongs to a higher context (ie a user or an
automated agent).
For most programs any kind of transaction retry is not worth the coding.
The code complexity and residual risk are just too great. Just propagate
the error out to the user or calling code and let them deal with it. You
just don't have the right context to deal with a server or network failures
in a meaningful way.
If you feel like you must deal with it, then propagate the exception out of
this method, and catch it at the level which knows how to retry the entire
transaction. Wait around for the instance to fail over to another server,
reconnect and issue the transaction again. If you know you are running
against a cluster with such high availability requirements that it the DBA's
must fail the instance over to perform routine maintenance then you can
expect this to happen, and you have no choice but to code around it. But in
any case the retry code does not belong in that method, but in an outer
controlling context.
David

Can errors occur while committing a tx?

Can an error occur /during/ the process of committing a transaction?
BeginTraction();
try {
UpdateTable1();
UpdateTable2();
CommitTransaction(); <-- error here?
} catch(Exception e){
RollbackTransaction();
}
Also, what are the implications in such a situation where both the
Updates pass without any errors, but committing the transaction fails?
Is this scenario even possible at all?
If so, what are the suggested best-practices for recovering from such
types of errors?
TIA,
Abdullah"Abdullah Kauchali" <none@.none.com> wrote in message
news:eT1t1UAzFHA.1264@.tk2msftngp13.phx.gbl...
> Can an error occur /during/ the process of committing a transaction?
> BeginTraction();
> try {
> UpdateTable1();
> UpdateTable2();
> CommitTransaction(); <-- error here?
> } catch(Exception e){
> RollbackTransaction();
throw;
> }
>
Remember to rethrow the exception!
You know, that's a really good question.
Yes errors are possible, but pretty darn unlikely (at least in SQL Server).
When you go to commit the transaction all of the changes have allready been
made to the tables, and written to the memory cache of the log file. So
almost everything that could go wrong already would have. There could
possibly be some error flushing the log to disk, or you could loose your
connection to the database server, or the server could just fail. If the
commit fails on the server, the transaction will be rolled back (or at worst
it won't be there when the database recovers). But from a client there's
probably some possiblility that the commit succeeds, but a network problem
prevents you from learning about it.
> Also, what are the implications in such a situation where both the
> Updates pass without any errors, but committing the transaction fails?
The transaction will be rolled back.
> Is this scenario even possible at all?
> If so, what are the suggested best-practices for recovering from such
> types of errors?
>
Treat it like a server or network failure.
It's so unlikely to happen in the first place, and you are so unlikely to be
able to recover if it does, that I would just pretend like it's impossible.
Just pretend like it can't happen. Your responsibility is only to keep the
database in a logically consistent state. You've done that by coding your
transaction. You are not responsible for making sure the transaction
suceeds. That responsibility belongs to a higher context (ie a user or an
automated agent).
For most programs any kind of transaction retry is not worth the coding.
The code complexity and residual risk are just too great. Just propagate
the error out to the user or calling code and let them deal with it. You
just don't have the right context to deal with a server or network failures
in a meaningful way.
If you feel like you must deal with it, then propagate the exception out of
this method, and catch it at the level which knows how to retry the entire
transaction. Wait around for the instance to fail over to another server,
reconnect and issue the transaction again. If you know you are running
against a cluster with such high availability requirements that it the DBA's
must fail the instance over to perform routine maintenance then you can
expect this to happen, and you have no choice but to code around it. But in
any case the retry code does not belong in that method, but in an outer
controlling context.
David

Can errors occur while committing a tx?

Can an error occur /during/ the process of committing a transaction?
BeginTraction();
try {
UpdateTable1();
UpdateTable2();
CommitTransaction(); <-- error here?
} catch(Exception e){
RollbackTransaction();
}
Also, what are the implications in such a situation where both the
Updates pass without any errors, but committing the transaction fails?
Is this scenario even possible at all?
If so, what are the suggested best-practices for recovering from such
types of errors?
TIA,
Abdullah
"Abdullah Kauchali" <none@.none.com> wrote in message
news:eT1t1UAzFHA.1264@.tk2msftngp13.phx.gbl...
> Can an error occur /during/ the process of committing a transaction?
> BeginTraction();
> try {
> UpdateTable1();
> UpdateTable2();
> CommitTransaction(); <-- error here?
> } catch(Exception e){
> RollbackTransaction();
throw;
> }
>
Remember to rethrow the exception!
You know, that's a really good question.
Yes errors are possible, but pretty darn unlikely (at least in SQL Server).
When you go to commit the transaction all of the changes have allready been
made to the tables, and written to the memory cache of the log file. So
almost everything that could go wrong already would have. There could
possibly be some error flushing the log to disk, or you could loose your
connection to the database server, or the server could just fail. If the
commit fails on the server, the transaction will be rolled back (or at worst
it won't be there when the database recovers). But from a client there's
probably some possiblility that the commit succeeds, but a network problem
prevents you from learning about it.

> Also, what are the implications in such a situation where both the
> Updates pass without any errors, but committing the transaction fails?
The transaction will be rolled back.

> Is this scenario even possible at all?
> If so, what are the suggested best-practices for recovering from such
> types of errors?
>
Treat it like a server or network failure.
It's so unlikely to happen in the first place, and you are so unlikely to be
able to recover if it does, that I would just pretend like it's impossible.
Just pretend like it can't happen. Your responsibility is only to keep the
database in a logically consistent state. You've done that by coding your
transaction. You are not responsible for making sure the transaction
suceeds. That responsibility belongs to a higher context (ie a user or an
automated agent).
For most programs any kind of transaction retry is not worth the coding.
The code complexity and residual risk are just too great. Just propagate
the error out to the user or calling code and let them deal with it. You
just don't have the right context to deal with a server or network failures
in a meaningful way.
If you feel like you must deal with it, then propagate the exception out of
this method, and catch it at the level which knows how to retry the entire
transaction. Wait around for the instance to fail over to another server,
reconnect and issue the transaction again. If you know you are running
against a cluster with such high availability requirements that it the DBA's
must fail the instance over to perform routine maintenance then you can
expect this to happen, and you have no choice but to code around it. But in
any case the retry code does not belong in that method, but in an outer
controlling context.
David

Can a commit fail??

Can an error occur /during/ the process of committing a transaction? So:
BeginTraction();
try {
UpdateTable1();
UpdateTable2();
CommitTransaction(); <-- error here?
} catch(Exception e){
RollbackTransaction();
}
Also, what are the implications in such a situation where both the
Updates pass without any errors, but committing the transaction fails?
Is this scenario even possible at all?
If so, what are the suggested best-practices for recovering from such
types of errors?
TIA,
AbdullahYes. If the log disk fills up, the write of the commit sentinal can fail,
thus it's indeed possible for a commit to fail. I'm sure that there are
other scenarios that could cause a commit failure. Therefore, if the commit
fails, the transaction should be rolled back (if that hasn't already
happened as a result of the failure).
"Abdullah Kauchali" <none@.none.com> wrote in message
news:ukGjDWAzFHA.1264@.tk2msftngp13.phx.gbl...
> Can an error occur /during/ the process of committing a transaction? So:
> BeginTraction();
> try {
> UpdateTable1();
> UpdateTable2();
> CommitTransaction(); <-- error here?
> } catch(Exception e){
> RollbackTransaction();
> }
> Also, what are the implications in such a situation where both the
> Updates pass without any errors, but committing the transaction fails?
> Is this scenario even possible at all?
> If so, what are the suggested best-practices for recovering from such
> types of errors?
> TIA,
> Abdullah|||Brian Selzer wrote:
> Yes. If the log disk fills up, the write of the commit sentinal can fail,
> thus it's indeed possible for a commit to fail. I'm sure that there are
> other scenarios that could cause a commit failure. Therefore, if the comm
it
> fails, the transaction should be rolled back (if that hasn't already
> happened as a result of the failure).
Thanks Brian.
Let's use your example. Suppose UpdateTable1() succeeds during the
COMMIT (SQL Server frees the resources and releases the locks for
Table1) and then attempts to commit statements in UpdateTable2() but
then realises "oops, transaction log is full!" Will UpdateTable1()
rollback on a rollback instruction? Won't the transaction logs still
be considered full for the rollback log entries to go through?
:)
(I am actually trying to understand the process of commit in a 2-phase
scenario (distributed transactions), but I'd like to understand the
concept from a local-transaction point of view first. So, I apologise
for my lack of knowledge there!
My question for the 2-phase (distributed) transaction is this: if the
DTC commits all preceding resources and then encounters a problem with
the very last resource in the chain of updates, can/does the DTC
actually "uncommit" the preceding resources it just instructed to
commit? No during the prepare phase, but during the commit phase.)|||The commit of UpdateTable1() succeeded, so a rollback isn't possible. The
commit of UpdateTable2() fails, the log file is full, and the database shuts
down. During recovery (assuming disk space has been freed or otherwise made
available), UpdateTable2() will be rolled back. Changes are written to the
transaction log before they're written to the database, and only after all
of the database changes have been flushed to the disk is the commit sentinal
written to the transaction log, so the recovery process can undo any changes
made by any uncommitted transactions.
I'm not sure exactly how the process works with a distributed transaction.
Maybe there's a different type of sentinal written to the transaction log
after the prepare phase has completed. During the prepare phase, all cached
changes in each participant are flushed to the disk and then a
ready-to-commit signal is sent back to the coordinator. Once the commit
signal has been sent, I don't think a rollback is possible, even if an error
occurs on one of the other participants. If communication is lost before
the commit signal is received, then the participant is required to roll back
the transaction. If it happens afterward, the transaction is supposed to be
committed. Again, I'm not sure exactly how the process works under the
covers. Maybe someone with more knowledge than I can give you a more
difinitive answer.
"Abdullah Kauchali" <none@.none.com> wrote in message
news:uUupV7EzFHA.1856@.TK2MSFTNGP12.phx.gbl...
> Brian Selzer wrote:
> Thanks Brian.
> Let's use your example. Suppose UpdateTable1() succeeds during the
> COMMIT (SQL Server frees the resources and releases the locks for
> Table1) and then attempts to commit statements in UpdateTable2() but
> then realises "oops, transaction log is full!" Will UpdateTable1()
> rollback on a rollback instruction? Won't the transaction logs still
> be considered full for the rollback log entries to go through?
> :)
> (I am actually trying to understand the process of commit in a 2-phase
> scenario (distributed transactions), but I'd like to understand the
> concept from a local-transaction point of view first. So, I apologise
> for my lack of knowledge there!
> My question for the 2-phase (distributed) transaction is this: if the
> DTC commits all preceding resources and then encounters a problem with
> the very last resource in the chain of updates, can/does the DTC
> actually "uncommit" the preceding resources it just instructed to
> commit? No during the prepare phase, but during the commit phase.)
>