Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How can I move data from my current table to an archive table when a delete is called on the table?

My current table and archive table needs to be in sync in their schema too.

Can someone help me?

Edit:
I am using MySql

share|improve this question
    
Which RDBMS are you using? –  Thomas Stringer Mar 4 '12 at 3:21

1 Answer 1

(This answer is assuming SQL Server)

-- create the audit table
select *
into YourTableDeleteAudit
from YourTable
where 1 = 2
go

-- create the trigger
create trigger YourTableDeletes
on YourTable
after delete
as
    insert into YourTableDeleteAudit
    select *
    from deleted
go

This would result in an audit table of a copy of the deleted records. I would recommend putting a datetime stamp on that (and maybe more fields for typical auditing). You could do that like this:

-- create the audit table
select *
into YourTableDeleteAudit
from YourTable
where 1 = 2
go

alter table YourTableDeleteAudit
add DeleteDate datetime null
go

-- create the trigger
create trigger YourTableDeletes
on YourTable
after delete
as
    insert into YourTableDeleteAudit
    select *, getdate()
    from deleted
go
share|improve this answer
1  
Another option in SS is to use the OUTPUT clause in the DELETE statement to archive in one step. –  JNK Mar 4 '12 at 13:38
    
What about RI that would be referencing the original table? –  Mark Stosberg Apr 16 '13 at 14:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.