Sometimes you may want all changes recorded to another table and keep them in sync.
There are of course a few different ways of doing this, you could play with binlogs and such. I got asked by a course attendee to do it in a specific way using triggers, so I wrote this up, and it should work:
mysql> CREATE TABLE t1 ( a INT, b CHAR(5) );
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE t2 ( a INT, b CHAR(5) );
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TRIGGER trg1 AFTER INSERT ON t1 FOR EACH ROW
-> INSERT INTO t2 VALUES (NEW.a, NEW.b);
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TRIGGER trg2 AFTER DELETE ON t1 FOR EACH ROW
-> DELETE FROM t2 WHERE a = OLD.a AND b = OLD.b LIMIT 1;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TRIGGER trg3 AFTER UPDATE ON t1 FOR EACH ROW
-> UPDATE t2 SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b LIMIT 1;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (1, 'a');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (2, 'a');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (2, 'b');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (2, 'b');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (2, 'b');
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM t1 WHERE a = 1;
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE t1 SET a = 1 WHERE b = 'b' LIMIT 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM t1;
+---+---+
| a | b |
+---+---+
| 2 | a |
| 1 | b |
| 2 | b |
| 2 | b |
+---+---+
4 rows in set (0.00 sec) mysql> SELECT * FROM t2;
+---+---+
| a | b |
+---+---+
| 2 | a |
| 1 | b |
| 2 | b |
| 2 | b |
+---+---+
4 rows in set (0.00 sec)