It's possible to see how the data in the table looked at a previous moment.

The table being defined as:

create table MyTable (

a int not null primary key,

b int,

c int,

description varchar (100)

)

The function MyTable_History will return the data from the table as existed at the @Date moment.

create function MyTable_History (@Date datetime)

returns @r table (a int primary key, b int, c int, Description varchar (100))

as

begin

insert into @r (a, b, c, Description)--make a copy of the table.

select a, b, c, Description

from MyTable

declare @inserted xml, @deleted xml

--We will apply all the changes made to the table in the reversed order

declare c cursor local for

select OldValues, NewValues

from DMLLogs

where DateCreated >= @Date

order by DMLLogID desc

open c

fetch next from c into @Deleted, @Inserted

while @@fetch_status = 0 begin

if @Inserted is not null and @Deleted is not null --update operation

update @r set

b = T.c.value ('@b', 'int'),

c = T.c.value ('@c', 'int'),

Description = T.c.value ('@Description', 'varchar (100)')

from @r R inner join @Deleted.nodes ('deleted') T(C)

on R.a = T.c.value ('@a', 'int')

if @Deletedis null --insert operation; must be deleted

delete from @r

from @r R inner join @Inserted.nodes ('inserted') T(C)

on R.a = T.c.value ('@a', 'int')

if @Inserted is null --delete operation; must be inserted back into the table

insert into @r (a, b, c, Description)

select T.c.value ('@a', 'int'),

T.c.value ('@b', 'int'),

T.c.value ('@c', 'int'),

T.c.value ('@Description', 'Varchar (100)')

from @Deleted.nodes ('deleted') T(C)

fetch next from c into @Deleted, @Inserted

end

close c

deallocate c

return

end

go

The shortcoming is that this function does not handle correctly cases where the primary key (column a) was changed as a result of a update operation.

Also it will be very slow on large tables. For those it will be more efficiently to directly update the table, as the following procedure does:

create procedure Recover_MyTable

@Date datetime

as

begin tran

declare @inserted xml, @deleted xml

declare c cursor local for

select OldValues, NewValues

from DMLLogs

where DateCreated >= @Date

order by DMLLogID desc

open c

fetch next from c into @Deleted, @Inserted

while @@fetch_status = 0 begin

if @Inserted is not null and @Deleted is not null --update operation

update MyTable set

b = T.c.value ('@b', 'int'),

c = T.c.value ('@c', 'int'),

Description = T.c.value ('@Description', 'varchar (100)')

from MyTable R inner join @Deleted.nodes ('deleted') T(C)

on R.a = T.c.value ('@a', 'int')

if @Deletedis null --insert operation; must be deleted

delete from MyTable

from MyTable R inner join @Inserted.nodes ('inserted') T(C)

on R.a = T.c.value ('@a', 'int')

if @Inserted is null --delete operation; must be inserted back into the table

insert into MyTable (a, b, c, Description)

select T.c.value ('@a', 'int'),

T.c.value ('@b', 'int'),

T.c.value ('@c', 'int'),

T.c.value ('@Description', 'Varchar (100)')

from @Deleted.nodes ('deleted') T(C)

fetch next from c into @Deleted, @Inserted

end

close c

deallocate c

commit tran

go

You can test the procedure using the following code:

begin tran

go

--load some data into the table

insert into MyTable (a, b, c, description)

select 1, 2, 3, 'first inserted'

union all

select 2, 3, 4, 'inserted'

waitfor delay '0:00:01' --wait a second because SQL has only 3 ms time resolution and we risk to have the same timestamp

declare @d datetime set @d = getdate() --store the time

waitfor delay '0:00:01'

-- change the data

insert into MyTable (a, b, c, description)

select 100, 2, 3, 'inserted'

union all

select 200, 2, 3, 'inserted'

update mytable set b = 5

delete from mytable where a = 2

select * from MyTable --watch the current data

exec Recover_MyTable @d --recover the old data

select * from MyTable --the changes are undone

go

rollback

That's it.

PS. Does anyone know how to insert tabs in the message ?