Using MySQL Triggers to Ensure Immutability

Developers are not always database admins, and database admins are not always developers; bootstrappers often have to be both. As we're building products, we're writing the code and designing the database, hopping back and forth between the two. It's fairly often that we just use the tool we know to accomplish the purpose, rather than using the optimal tool. Sure, you can pound a nail in with a set of vice grips, but wouldn't a hammer make more sense?

Let's talk about databases.

#Solving it With Code

In building the invoicing piece of Mantis, I've created a "user status history" table that keeps track of when employees change plans, are no longer employed, or are no longer hourly workers (therefore are not billed). Anytime an employee's attributes change, a new record is created in the status history table that shows all the employee's current attributes and the old record is completed by inserting the end time of the previous plan.

Take a look at the picture below to get a better understanding.

The employee's current state is always the record with the NULL value in the to field, indicating that that state has not yet come to an end. Employee #1 went from hourly to non-hourly (records 1&2). Employee #6 went from active to inactive (records 5&10).

I use this data to build my invoices. If an employee got let go (went inactive) halfway through the month, I'm not going to charge them for a whole month, so I need to know when they went inactive. Because this is a historical table that I'll be basing invoicing on, I want to ensure that this data can never change.

My first thought was to copy the data over to another table that would separate the data from the status history table and put it in a different table that is only used to run invoices. That may seem like an ok solution, but it's not. You really shouldn't be duplicating data in your database like that.

I kept trying to think up a way to solve the problem with Yii, because that was the hammer that I had, the one that I use every day. The solution really lies in the database itself, below the application altogether. I needed to get some advice from someone who really knows databases, so I called my Dad. He's been doing this stuff for years.

#Enter: Triggers

My dad educated me about Triggers in MySQL. Triggers are like hooks that allow you to execute logic based on events, a lot like Yii has "before" and "after" filters. These triggers allow you to keep some basic data integrity logic out of your application layer and put that burden on the database, keeping your code uncluttered and your data safe. Let me show you the SQL and then I'll walk you through it.

DELIMITER $$
 
CREATE TRIGGER lock_down BEFORE UPDATE ON tbl_user_status_history
    FOR EACH ROW BEGIN
        IF old.to IS NOT NULL THEN
            SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'RECORD IS LOCKED, CANNOT UPDATE';
        END IF;
 
        IF (old.id != new.id OR
            old.user_id != new.user_id OR
            old.from != new.from OR
            old.hourly != new.hourly OR
            old.active != new.active OR
            old.plan != new.plan) THEN
            SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'CANNOT CHANGE RECORD VALUES';
        END IF;
    END$$
 
CREATE TRIGGER no_delete BEFORE DELETE on tbl_user_status_history
    FOR EACH ROW BEGIN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'CANNOT DELETE RECORD';
    END$$
 
DELIMITER ;

The first thing this does is set the delimiter to "$$" so we can safely use the semicolon elsewhere. We set it back to the semicolon at the end.

The create trigger syntax is pretty straightforward: you give the trigger a name, declare the event that the trigger is based on, the table, and then the body of the trigger. In my case I've name my first trigger lock_down, the action is BEFORE UPDATE, and the table is tbl_user_status_history.

Now: the body. The body of the statement starts checking if the old to field was NULL before the update. If the old to field was not NULL, that means that record should be set in stone, so we throw an error by using the SIGNAL statement. So now we've taken care of the instance when the record has been completed, it can't be changed.

Another case we need to worry about is when the to field is NULL, we need to make sure that only the to field can be updated. We don't want the status to be changed once it has been set, we just want to be able to close out that particular row. That's what the second IF statement does: it compares every old value to every new value (except the to field, obviously) and if any of them don't match, we throw an error. This way, any value change creates an error, but you are allowed to update the to field.

The second trigger we need to create is a trigger that makes sure you can't delete a row. This is our BEFORE DELETE trigger, and it's super simple. We just throw an error, every time. Pretty simple.

Now our history records are truly historical records: they cannot change. I'm confident that I can calculate an invoice upon these records and the invoices will always be the same. This keeps our PHP code nice and clean, leaving it up to the DB engine to enforce these (extremely important) rules. It also gives us a sense of security, knowing that no matter what happens, these records will always be correct.