Code Spatter

Setting constraints and rules in the database is better than writing special code to handle the same task since it will prevent another developer from writing a different query that bypasses all of the special code and could leave your database with poor data integrity.

For a long time I was copying info to another table using a script since MySQL didn’t support triggers at the time. I have now found this trigger to be more effective at keeping track of everything.

This trigger will copy an old value to a history table if it is changed when someone edits a row. Editor ID and last mod are stored in the original table every time someone edits that row; the time corresponds to when it was changed to its current form.

DROP TRIGGER IF EXISTS history_trigger $$
 
CREATE TRIGGER history_trigger
BEFORE UPDATE ON clients
    FOR EACH ROW
    BEGIN
        IF OLD.first_name != NEW.first_name
        THEN
                INSERT INTO history_clients
                    (
                        client_id    ,
                        col          ,
                        value        ,
                        user_id      ,
                        edit_time
                    )
                    VALUES
                    (
                        NEW.client_id,
                        'first_name',
                        NEW.first_name,
                        NEW.editor_id,
                        NEW.last_mod
                    );
        END IF;
 
        IF OLD.last_name != NEW.last_name
        THEN
                INSERT INTO history_clients
                    (
                        client_id    ,
                        col          ,
                        value        ,
                        user_id      ,
                        edit_time
                    )
                    VALUES
                    (
                        NEW.client_id,
                        'last_name',
                        NEW.last_name,
                        NEW.editor_id,
                        NEW.last_mod
                    );
        END IF;
 
    END;
$$

Query Breakdown

Notice where all of the semi-colons are. If you leave some out, it may take hours to debug your trigger syntax since the error messages don’t always help.

The OLD and NEW keywords correspond to the value in the row before an update and what is being inserted. Compare these values for each column that you want to track in the table.

An AFTER INSERT trigger should also be used to copy the initial data, and a BEFORE DELETE trigger would be good if you actually remove rows from the database instead of doing a soft delete like I normally do.

Delimiter is Tricky

$$ is being used to delimit the end of the two queries since the semi colons are needed for the interior statements.

Command Line Delimiter

To use this create-trigger query at the command line, you can add

DELIMITER $$

before the trigger statement and

DELIMITER ;

after it.

phpMyAdmin Delimiter

If you are using phpMyAdmin, there should be a small box below the main SQL box where you can enter $$.

Delimiter in php Scripts

If you would like to have php scripts create triggers you will need to use the mysqli_multi_query function. Below is an example.

$con = new MySQLi('host', 'user', 'pass', 'db');
 
$sql = '
DROP TRIGGER ... ;
CREATE TRIGGER ... ;
';
 
$con->multi_query($sql);

Notice that there isn’t a need for any special delimiter since the multi_query function applies it for you.

Privileges

Make sure the database user has the Super privilege since that is needed to create triggers.

Related posts:

1. Careful with Numeric Data Types in MySQL  TINYINT can be set to length of 4, but that doesn’t mean the max value is 9999. A tiny int...
2. Tips for MySQL to PostgreSQL Switch  If you’ve decided to move a few tables from MySQL to PostgreSQL, these few tips might help. I won’t get...
3. How to Break a MySQL Left Join  Care must be taken when placing conditions on the results of the right-hand table of a LEFT JOIN because it...