Is this a reasonable way to maintain a log/audit table of changes to a mysql table? [closed]

Closed. This question is opinion-based. It is not currently accepting answers. 开发者_StackOverflow

---

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.

Closed 9 years ago.

Improve this question

I'm wanting to maintain log tables of changes to several tables in my schema. I'm trying to set it up so that I don't need to list all the fields in my insert statement, so I have set up the log table to have the exact same structure as the master table - I'm just removing the auto-increment/primary key and making an adjustment to a timestamp.

Once I've established that this works, I'm planning to automate the set-up of the log tables and the triggers with a bit of dynamic sql.

Is this a reasonable approach? Any problem with not having any primary key on the log table?

CREATE member (
    id INTEGER AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

-- Add change log table
CREATE TABLE change_log.member_log LIKE member;
ALTER TABLE change_log.member_log CHANGE id id INTEGER;
ALTER TABLE change_log.member_log CHANGE last_updated last_updated DATETIME;
ALTER TABLE change_log.member_log DROP PRIMARY KEY;
DELIMITER $$
    CREATE TRIGGER log_member_changes_on_update BEFORE UPDATE ON member FOR EACH ROW 
    BEGIN 
        INSERT INTO change_log.member_log SELECT * FROM member WHERE id = NEW.id; 
    END$$
DELIMITER ;

Many thanks

---

You might want to add an index on the id column if you expect to select by id often. Furthermore, it would probably be helpful if you could add information about who made the change. And maybe you want to consider logging inserts and deletes too? Other than that I think it is a perfectly reasonable approach.

---

You should consider:

1. Create a table called "Audit";
2. Create a record in that table every time user creates a new record or update one;

3. This table should have:

   • the name of the table that was updated,
   • the number of the record,
   • the name or id of the user that made the change,
   • the action taken, like 'updated address' or 'new client created',
   • the timestamp of the moment that update was made

You have to insert data to this "Audit" table in the software, not from any trigger in the database or you will need to copy the trigger for each table. Programming you could do one method to handle every table once.

---

It's basically a good idea. You probably want to add triggers for all events, ie insert, update and delete. With delete you'll have to insert the current timestamp into the audit table manually, as delete wont update the timestamp column.

Consider storing both old and new values in the audit table (ie double the number of non-id columns) because searching for when a value changed is difficult when only the current value is logged, but when you log both, it's easy:

where old_column1 != new_column1

It also makes finding the value at a particular time easy:

where someDateTime between old_last_updated and new_last_updated

I have successfully used exactly this approach.