One of our developers wants to implement undo stuff in the data tier.  His
idea was to make a trigger on a table that loops over the columns in
[inserted] and [deleted] to compare the values, find out what changed to
what and then save it in a vertical undo table.

the undo table might look like this:

CREATE TABLE dbo.Undo (
    TableName NVARCHAR(255),
    ColumnName NVARCHAR(255),
    RecordKeyValue NVARCHAR(255),
    OldValue NVARCHAR(255),
    NewValue NVARCHAR(255),
    WhenItHappened DATETIME
)

Trouble is, inside a trigger, there does not seem to be any way to cleanly
loop over the columns of [inserted] or [deleted] and doesn't seem to be a
way to get the name of the table or its columns.  Currently, it looks like
he will have to hard-code it all.

Anyone ever have to implement a record-level undo and have some hints for
us?

What about creating a dummy #temp table with the structure of the Inserted
results and loop through theses ones?

SELECT * INTO #TempTable
Select * from INSERTED Where 1=2

--Will produce a empty table with the structure of inserted.

Then you should be able to use the INFORMATION_SCHEMA.COLUMNS to get the
column names.

HTH; Jens Suessmeyer.

---
http://www.sqlserver2005.de
---


Show quoteHide quote

How often do you need this? It will generate a lot, lot, lot of overhead if
you do it this way.

In general for this kind of functionality you either use transactions if the
undo's happen within a short timeframe of the update, or get a tool like
Lumigent's Log Explorer if they happen in a long timeframe after the update,
and you only need it irregularly. If it is a specific business requirement
to keep track of these changes, you usually only need it on a very limited
number of tables, and the best thing to do then is add a datetime column to
the primary key, and always insert and never update (which has a few other
issues, but you can sort that out).

Show quoteHide quote

Mike Labosh wrote:
Show quoteHide quoteHave you thought about just using history tables that match the design
of the production tables and have some additional columns like AuditType
(Insert, Update, Delete), AuditDate, and AuditUser, etc. You could
prepopulate the history tables with copies of what appears in production
for start things off or just feed off the deleted table as needed. As
each row is changed, it gets dumped to the history table easily from the
inserted and deleted tables. For a delete, you just dump deleted. For
Inserted, just inserted. And for updates you can dump the before and
after data from both deleted and inserted. Recovery of data values
should be a relatively simple task of looking at the history table and
selecting the row to recover.

I guess a question I have is your application Client Server, or ASP?

The trigger solution with Audit tables works well but there are all kinds of
issues. If this is a Client server app, and all data is kept client side then
undo's are easy, hold original changes somewhere until the old data falls out
of scope. But in a stateless environment the data is always out of scope.

If a user makes a change to a set of data, is there a possibility for
another user to make a change to all or some of the same data after the first
transaction is committed?
If so how can you possibly undo the first users change if a second user
changed the value to something else?
I have done it a couple of different ways, but it is never elegant, and
always problematic.


Show quoteHide quote

If the DB is designed in any manner that makes it perform well, it would be
one of the higher n-forms. Trying to roll back one table plus it's 4 or 5 or
+ associated tables becomes rather complicated using a single auditing table
policy.

If he needs a undo table, a perfect 1:1 match with a history table does
perform better, and the obvious NVARCHAR(255) performance hit will go  away
too (assuming he did not use NVARCHAR(255), for every column in the DB, but
that is another debate).

Generic table = mediocre performance, if not bad performance. As him if he
can spell contention.

If 1 million columns change in on day, you generate 1 million rows in this
audit table. How does he want to manage this and is he going to buy you a
Unisys ES 7000?

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: m***@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

Show quoteHide quote

Do this in your trigger to obtain the table name.

DECLARE @TableName nvarchar(128)
SELECT @TableName = OBJECT_NAME(parent_obj) from sysobjects WITH(NOLOCK)
WHERE id = @@PROCID


I use a different approach.  I create a historical table for each tracked
table.
Each current table has an IDENTITY column, and a BIGINT DBActivityKey column
Each historical table is a mirror image of the current table, except there
isn't any IDENTITY property and instead of one BIGINT DBActivityKey column,
there are two BIGINT DBIntervalStartKey, DBIntervalStopKey.

The above ActivityKey columns are linked to an activity table

CREATE FUNCTION dbo.[CONTEXT_INFO]()
RETURNS BINARY(128) AS
BEGIN
RETURN (SELECT context_info FROM master.dbo.sysprocesses WITH(NOLOCK) WHERE
spid = @@SPID)
END
GO
CREATE TABLE [DBActivity]
(
[DBActivityKey] BIGINT IDENTITY(-4611686018427387904, 1) NOT NULL
  CONSTRAINT [PK_DBActivity]
   PRIMARY KEY CLUSTERED
    ON [HISTORY],
[id] INT NOT NULL,
[Action] TINYINT NOT NULL
  CONSTRAINT [CK_DBActivity_Action]
   CHECK ([Action] = SIGN([Action])),
[SUSER_SID] VARBINARY(85) NOT NULL
  CONSTRAINT [DF_DBActivity_SUSER_SID]
   DEFAULT (SUSER_SID()),
[DATE] DATETIME NOT NULL
  CONSTRAINT [DF_DBActivity_DATE]
   DEFAULT (GETDATE()),
[APP_NAME] NVARCHAR(128) NOT NULL
  CONSTRAINT [DF_DBActivity_APP_NAME]
   DEFAULT (APP_NAME()),
[CONTEXT_INFO] BINARY(128) NOT NULL
  CONSTRAINT [DF_DBActivity_CONTEXT_INFO]
   DEFAULT (dbo.CONTEXT_INFO()),
[PROCID] INT NOT NULL
  CONSTRAINT [DF_DBActivity_PROCID]
   DEFAULT (@@PROCID),
[ROWCOUNT_BIG] BIGINT NOT NULL
  CONSTRAINT DF_DBActivity_ROWCOUNT_BIG
   DEFAULT (ROWCOUNT_BIG())
) ON [HISTORY]
GO

I use INSTEAD OF triggers on each tracked table

For an insert, a row is inserted into DBActivity, the DBActivityKey is
obtained using SCOPE_IDENTITY(), and the new records are inserted into the
current table along with with the new DBActivity value.

For an update, a row is inserted into DBActivity, the rows from the deleted
table are inserted into the historical table using the existing
DBActivityKey as DBIntervalStartKey, and new newly generated DBActivityKey
as DBIntervalStopKey, then the rows in the current table are updated with
the values from the inserted table, with the exception that the newly
generated DBActivityKey replaces the existing one.

For a delete, a row is inserted into DBActivity, the rows from the deleted
table are inserted into the historical table in the same manner as for an
update, then the rows in the current table are deleted.

This mechanism allows tracking the entire lifetime of a row, from its
creation through its deletion.  For the tables tracked, you can find out
what the state of those tables were at any arbitrary point in time.

Note that no record is inserted into the history table for an insert,
because the new row contains current values and the history table contains
all of the old values.

It's a good fit for a database that has a lot of inserts, and few updates.
There isn't a lot of overhead, because there is one row per database
activity in DBActivity, and for inserts, there are 8 bytes added to each
row.  For updates, and deletes, a copy of the row + 8 bytes is stored in a
historical table.  If you use set-based updates (as you always should try
to), it only adds 1 DBActivity row for each activity, so if you're updating
100 records, 101 records will be inserted, 100 in the historical table and 1
in DBActivity.  Disk space is really cheap these days.  I just bought 2
250GB drives (500GB) for just over $300.

How would it solve your problem?  Undo is as simple as replacing the values
for a row in the Current table with the values in the top row in the
historical table.  (It could get a bit complicated, if you need to undo
several times.)

Current Table
   Current information

Historical Table
   Row state before last update
   Row state before second-to-last update
   ...
   Row state after first change




Show quoteHide quote

Mike, you really are in Hell, aren't you? I am sorry to say that in a
newsgroup, but every one of your postings reads like "We did not notice
the flood because we were too sick from the Black Death."  Comedy=
happens to the other guy  Tradegy = Happens to me.

This kind of thing is best done in a generational concurrency RDBMS --
Firebird, Interbase, etc.  It is a screaming bitch in an RDBMS based on
locks.  See the work for the last 20 years by Jim Grey.

To be honest, my recommendation would be to do this in the business tier. This
type of problem is one that the data tier is generally not well equipped to
handle. It *sounds* like it should be possible, but the reality is that there
are a host of issues that make untenable in the data tier.


Thomas




Show quoteHide quote

Transaction in SP
Case Statement?
Adding non-table field to a view?
I'm confused need some help ... with Union
problem with context info