Move instead of Delete 1

[andyd273]

I want to set up a system so that when a user tries to delete a record from the database it automatically moves the record to a mirror database, to act kind of like a recycle bin, so that in case someone accidently pushes the wrong button, we wont loose valuable information.
I know I can use triggers set on each table to accomplish this (though if anyone has a suggestion on how to write this trigger I would be super happy) but is there a way to do this database wide without having to alter and test all 50+ tables?

 

[mutley1]

Hey Andy,

You cant set it at DB level - it needs to be done on each table. Without knowing a bit more about your system, here's the basics.

1. I assume you will have a replica table (i.e. original is called tbl1 and you will have a tbl2 that is exactly the same to store the deleted data).
2. Create the trigger (use the below code but rename the tables - you should be able to work out which ine is which, but leave the deleted. This is where the deleted stuff is temporarily held during deletion.
3. Delete something out of table1 and see what appears in table2!!

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ALTER  TRIGGER [deletecopy] ON [dbo].[table1]
FOR DELETE 
AS 
insert table2
select * from deleted 
GO 
SET QUOTED_IDENTIFIER OFF 
GO 
SET ANSI_NULLS ON GO

Cheers,

M.

 

[andyd273]

Alrighty then,
Basicly the setup is SQL Server 2000.
the main database is 'JL'
and the insert into database is 'JLDel'

so the trigger should look something like:

Create TRIGGER [deletecopy] ON [JL].[dbo].[tblCustomerComments]
FOR DELETE
AS
insert [JLDel].[dbo].[tblCustomerComments]
select * from deleted

Which returns:

Server: Msg 8101, Level 16, State 1, Procedure deletecopy, Line 4
An explicit value for the identity column in table 'JLDel.dbo.tblCustomerComments' can only be specified when a column list is used and IDENTITY_INSERT is ON.

 

[andyd273]

I removed the identity from the id fields (Since they don't matter) and that seems to have fixed the error.
also I set it to After Delete because it wasnt deleting with For Delete...


Alter TRIGGER [tblCustomerComments_delete] ON [JL].[dbo].[tblCustomerComments]
AFTER DELETE
AS
insert [JLDel].[dbo].[tblCustomerComments]
select * from deleted

 

[mutley1]

Err...OK, so is it working or do you need any more help andy?!

BR,

M.