I came across a situation where I needed to make a backup of every row either newley insterted, or updated in my database. I didn’t want to share the latest backed up data on the same table, but then needed a way to reference the pk id of the original row when the data from that row was created for the first time.
MySQL has a handy function called
LAST_INSERT_ID() which get’s the last inserted row id just executed. CodeIgniter also has a very easy way to get this value
$this->db->insert_id();, which can be assigned to a variable and used with an insert statement, to create a reference to the backup of the new row in the backup table. You'll probably also want to do this under a table lock to prevent concurrent operations.
I don’t know whether this is the best way to do backups. This is just something I came up with. I should probably lookup how others do it at some point :P