Simple MySQL: using TRIGGERs to keep datetime columns updated without direct SQL calls

If you’ve ever used non-opensource code, or applications that you don’t have complete control over, then you may have run into situations you need to alter data on a per-row basis but been unable to do so for lack of application SQL access. The solution to this type of problem is to use a MySQL TRIGGER, which allows us to execute arbitrary SQL commands when defined events occur. Why is this useful and how does it work? Well…

For example, I have a freeRADIUS server that uses MySQL as a backend for the user authentication, and one of my server applications (HostBill) provides a freeRADIUS plugin that allows my users to manage their RADIUS accounts; however the default freeRADIUS schema lacks a DATETIME column on the user table. When a user is created (INSERT) or has their password changed (UPDATE) I have no row data that tells me the dates when these operations were issued. Typically this would be a trivial change: issue an ALTER TABLE statement to add two columns and then add some NOW() statements to the application’s SQL calls.

However, the problem is that the application in question is ion-cube encrypted so I cannot make SQL changes to add support for my ‘date_created’ and ‘date_modified’ columns. I could intercept the TCP steams via MySQL Proxy or a custom script but that’s quite silly to have to do for this. Other methods also exist but are rather hack-ish.

MySQL TRIGGERs easily solve this situation. Here are two TRIGGERs, one that runs on INSERT and the other on UPDATE. Now the database can have ‘date_created’ and ‘date_modified’ data without any application changes. Whenever a row is inserted into the table the ‘date_create’ column will be populated via the NOW() function and correspondingly when a row is modified the ‘date_modified’ column will be updated with the NOW() function.

DELIMITER |
CREATE TRIGGER trigger_radcheckDatetimeInsert BEFORE INSERT ON radcheck FOR EACH ROW 
BEGIN
    SET NEW.date_create = NOW();
END; 
|
CREATE TRIGGER trigger_radcheckDatetimeModify BEFORE UPDATE ON radcheck FOR EACH ROW 
BEGIN
    SET NEW.date_modify = NOW();
END; 
|
DELIMITER ;
Tagged , , ,

3 thoughts on “Simple MySQL: using TRIGGERs to keep datetime columns updated without direct SQL calls

  1. Todd Farmer says:

    Good examples of trigger use cases in MySQL! It’s worth noting that MySQL 5.6 adds support for defining DATETIME or TIMESTAMP columns that automatically update on creation or modification. It’s a useful extension of the familiar TIMESTAMP behavior that MySQL has had for some time:

    http://mysqlblog.fivefarmers.com/2012/05/29/overlooked-mysql-5-6-new-features-timestamp-and-datetime-improvements/

  2. admin says:

    That’s good news Todd — I’ve been waiting for those features for literally years.

  3. [...] Matt Reid is blogging about using TRIGGERs to keep datetime columns updated without direct SQL calls. [...]

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>