How to log errors within a transaction and survive a ROLLBACK

Within a transaction, want to be able to log errors to an activity log
table but if errors occur and a ROLLBACK is invoked the errors logged
to the activity log table get rolled back also. What's the best way
to handle this situation?william@.accupointe.com wrote:
> Within a transaction, want to be able to log errors to an activity log
> table but if errors occur and a ROLLBACK is invoked the errors logged
> to the activity log table get rolled back also. What's the best way
> to handle this situation?
Write the data in the log table after you perform the rollback (keep
the values in some variables when you rollback). If you need to log
multiple rows, you can temporarily store the values in a table
variable, which is not affected by a ROLLBACK.
--
Razvan Socol
SQL Server MVP

How to log any activity

Dear friends,
I'm working on an entity relationship diagram about a mice farm.
Let's say we have a very very basic diagram like this:

MOUSE "is in" CAGE

For example, this gives

Mouse "n1500" is in cage "AAA"
Mouse "n1501" is in cage "BBB"

One operator can take one mouse and put it into another cage, for
example the first line becomes

Mouse n1500 is in cage CCC

Now, I was asked to track any movement... I ask myself: do I need to
add

OPERATOR "moves" MOUSE

so that I have

Operator "John" moves Mouse "n1500" on "Monday 3rd, 2007" at "5 PM"
from "AAA" to "CCC"?

or this kind of logging is provided some way by SQL server?

Thanx a lot for any hint.Not automatically.

You could add a trigger and an audit table to your database, so that every time a record is inserted updated, the pertinent data would be logged to the audit table.

ref Using TRIGGERS to add an audit trail to a table (http://www.xtremevbtalk.com/showthread.php?t=217047)

Note that the example shown doesn't audit the data which was changed, but that is easy to add.

Note: Since you're asking specifically about SQL Server, I'm moving this thread there (to the SQL Server Forum).