A Trigger to show when a row was last updated

It is a good idea to be able to tell when a row has been updated on your table.
Here is a simple way of doing that. It's not perfect auditing, for that you would need to write a trigger to store the before and after values in a table that the normal user could not write to, but it's a start.

Add a column to our table to hold the date stamp:

CREATE TABLE cddb.menus (
   name      VARCHAR(20) NOT NULL,
   itemno    INT NOT NULL,
   itemtext  VARCHAR(50) NOT NULL,
   itemlink  VARCHAR(150) NOT NULL,
   last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

and write a simple trigger to fire off when the table is updated:

CREATE TRIGGER cddb.t_menus_1
BEFORE UPDATE
ON cddb.menus
FOR EACH ROW SET
new.last_updated:=CURRENT_TIMESTAMP();

The results we get are:

mysql> select * from menus;
+----------+--------+-----------------+------------------+---------------------+
| name     | itemno | itemtext        | itemlink         | last_updated        |
+----------+--------+-----------------+------------------+---------------------+
| mainmenu |      1 | Data Entre      | dataentry.php    | 2009-05-11 13:57:13 |
| mainmenu |      2 | Data Lists      | datalists.php    | 2009-05-11 13:57:13 |
| mainmenu |      3 | Reports         | reports.php      | 2009-05-11 13:57:13 |
| listmenu |      1 | List Publishers | publishers.php   | 2009-05-11 13:57:13 |
| listmenu |      2 | List Artists    | artists.php      | 2009-05-11 13:57:13 |
| listmenu |      3 | CDs by Artist   | lcdsbyartist.php | 2009-05-11 13:57:13 |
| listmenu |      4 | Tracks by CD    | track.php        | 2009-05-11 13:57:13 |
+----------+--------+-----------------+------------------+---------------------+
7 rows in set (0.02 sec)

mysql> update menus set itemtext = 'New item' where name = 'mainmenu' and itemno=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from menus;
+----------+--------+-----------------+------------------+---------------------+
| name     | itemno | itemtext        | itemlink         | last_updated        |
+----------+--------+-----------------+------------------+---------------------+
| mainmenu |      1 | New item        | dataentry.php    | 2009-05-11 13:59:38 |
| mainmenu |      2 | Data Lists      | datalists.php    | 2009-05-11 13:57:13 |
| mainmenu |      3 | Reports         | reports.php      | 2009-05-11 13:57:13 |
| listmenu |      1 | List Publishers | publishers.php   | 2009-05-11 13:57:13 |
| listmenu |      2 | List Artists    | artists.php      | 2009-05-11 13:57:13 |
| listmenu |      3 | CDs by Artist   | lcdsbyartist.php | 2009-05-11 13:57:13 |
| listmenu |      4 | Tracks by CD    | track.php        | 2009-05-11 13:57:13 |
+----------+--------+-----------------+------------------+---------------------+
7 rows in set (0.00 sec)

mysql>