Phil Latio wrote:
> I have 2 virtually identical tables and wish to move data between them.

> I have some ideas for workarounds such as actually not deleting the record
> in the suspended table but instead adding an extra field to act as a flag.

Unless this flag causes you some dramatic speed issues to your queries
or other problems, I would suggest you to do this, but perhaps a little
differently than you are planning. Instead of just adding a flag to your
first table, add the flag and remove the other table complitely. Use the
flag to identify the location for the record, or even history also. The
flag could for example have values 0=new in suspend, 1=in live table,
2=back to suspend. Or what ever fills your needs.

There are a lot of benefits with the flag-style when compared to
identical tables.

For one, instead of insert-delete-insert-delete you only need to do
update-update, which is much faster and more safe (lesser chance for
data loss), your database structure would also be more simple, if you
had only one table, instead of two identical. Queries that need data
from both tables would also be propably faster. And you wouldn't need to
lock tables during the "move" as it happens within single query, to
avoid problems that might appear in multi-user environments. Also moving
several or all records within a single query would be trivial.

So instead of adding triggers to your delete queries, I would suggest
you to modify the database and rewrite your delete-insert queries to a
single and fast update queries. Unless of course this is for some reason
problematic.