数据库mysql编辑,批准数据库编辑-mysql

I currently have a database which many users can access and make changes to. The is also a log database that stores all changes to tables within the database using triggers.

I would like to add the ability to approve edits before they are changed in the database.

What would be the best way to go about this?

解决方案

We have something similar on one of our sites, we've added a bunch of tables:

users sites ... etc

Then we have a bunch of shadow tables:

users-shadow sites-shadow ... etc

The shadow tables have identical structures to the real tables except for an added line for the user who made the change. So first we use this query when a change is submitted by a user who needs to have his/her database actions approved:

REPLACE INTO users-shadow (user_mod,id,username,password,salt...) VALUES (16,50,'bob','stuff','salt'...);

Obviously, make sure this isn't open to injection, use prepared statements etc.

When approved, a row in the shadow table is simply removed from the shadow table, the user_mod value dropped and changes (non-null values) inserted into the real table (or updated if an id is specified, using REPLACE syntax). We do this logic in perl so sadly don't have any SQL on hand for it.

Remember that SQL REPLACE does a DELETE and an INSERT rather than an UPDATE. You will need to amend any triggers to allow for this behaviour.

Note: The reason we didn't use an 'approve' flag was that we needed the ability to amend existing records, of course we couldn't have multiple records with the same primary key.