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

StackExchange Database Administrators Q#14338, answer score: 4