Safe delete idea for discussion

[John Rivers]

Hello,

I think this will apply to alot of web applications:

users want the ability to delete a record in table x

this record is related to records in other tables
and those to others in other tables etc.

in other words we must use cascade delete to do
this in one go

my problem is that users make mistakes

and one day they will accidently delete the wrong
record in table x

and the cascade delete will take out a large chunk
of the database

and they will ring me and say "we want to put it back"

that got me thinking about some approaches

1 - have a little ui that shows them all the records
they are about to delete so they think see the
consequences of their actions, this will lower the
number of mistakes

2 - leverage the current interface to guide them through
the cascade manually (ie: not in sql) so that they are
forced to confront the consequences bit by bit
and it takes a long time so it is dull and they won't
do it in a whim

but then i realised whatever i do they will make mistakes
and why write code when you don't have to

thus i came up with this approach that seems to fulfill
all requirements and take almost no coding whatosever:

enable cascade delete in the database

create a delete trigger on the table in question
that writes an entry into a DeleteLog table
including three pieces of information:

timestamp (of the sql box!)
tablename (of the table with the record to be deleted)
recordid (of the deleted record or records)

those two things wrapped in a transaction
to stop the risk of updates to the target
record after the log entry and before the delete

now!

users can delete whatever they like
and when they make a mistake (infrequently)
i can use a prewritten script to do a point
in time restore of the database
(to a different dbname - i call this docking)
using timestamp in the deletelog table
and run an easy but boring to write script that pulls
the data back in

what do you think?

 

[John Rivers]

Cripes that was easy:

create table LogDeletions (
LogID int primary key identity
, LogDate datetime not null default getdate()
, TableName varchar(255) not null
, RecordID int not null
)


create trigger trAuditLogDelete
on auditlog
for delete
as
--
insert into LogDeletions (LogDate, TableName, RecordID)
select getdate(), 'AuditLog', AuditLogID
from deleted
--(end)

why didn't i think of this years ago?
100% reversable deletions, yeah!

 

[John Rivers]

Wow, I'm flying this morning!

--example point in time restore script

drop database Recovery
go
restore database Recovery
from disk = 'filename of last full backup', norecovery
go
restore log Recovery
from disk = 'filename of suitable log file', stopat = '2005-05-05
12:01:32.435'
go

--example recovery script for simple chain of 3 tables
--maintable (1-*) firstdependenttable (1-*) seconddependenttable

select primarykey into #temp1
from firstdependenttable where maintable.foreignkey = the one deleted
from the maintable

insert into originaldatabase.dbo.seconddependenttable
select *
from seconddependenttable
where firstdependenttable.foreignkey in (select primarykey from #temp1)

insert into originaldatabase.dbo.firstdependenttable
select *
from firstdependenttable
where primarykey in (select primarykey from #temp1)

insert into originaldatabase.dbo.maintable
select *
from maintable
where primarykey = the one deleted from the maintable

the goods news is, that however complicated your database
scheme, relationships etc. a dependency tree
can always be reduced to a simple ordered list

so this pattern can be extended to handle any
database schema

so now we could move to the next level
and write code to examine the database schema
and generate the data insertion code above
automatically

plus another bit of code to track
the database backup files and
work out the right restore script

but that would be over doing it

that is the solution finished

i hope you like it

 

[John Rivers]

Just thought of a small problem

the getdate() inside the trigger

might end up returning a time AFTER

the delete time in the log file

no way to know how sql server does that internally

if that is the case the solution would be

to use a stored procedure instead of trigger

something like: (just a sketch)

create proc spAuditLogDelete
@AuditLogID int
as
--
declare @returnvalue int
set @returnvalue = 1
--
begin tran
if @@error <> 0
goto label_end
--
insert into LogDeletions (LogDate, TableName, RecordID)
select getdate(), 'AuditLog', AuditLogID
from deleted
if @@error <> 0
goto label_rollback
--
delete
from auditlog
where auditlogid = @auditlogid
if @@error <> 0
goto label_rollback
--
commit tran
if @@error <> 0
goto label_rollback
--
set @returnvalue = 0
goto label_end
--
label_rollback:
rollback tran
--
label_end:
return @returnvalue
--(end)

which is fine and dandy but a bit less robust

or just take of a couple of milliseconds off the timestamp???