Soft delete: Copy row data to archive table when deleting 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
(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