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 ;