Exploring Triggers in MySQL and PostgreSQL

Recently I was working on a car maintenance database. The database stores, among other things, odometer reading, miles per fill up, number of gallons per fill up, and cost per gallon. One of the missing pieces of information I wanted to report on is the miles per gallon I was achieving. While this is trivial to insert, I didn’t want to modify the client which was already deployed and running. Since the data to determine this value was already present, I turned to database triggers to help me record this value.

Wikipedia describes “A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database.”

In our case, the procedural code we want to execute is the computation of our miles per gallon, and the event we are responding to is an insert into our table. In this article I will outline how to create these triggers in both MySQL and PostgreSQL.

Our triggers will be applied to the following table.

CREATE TABLE mileage (
   miles float,
   gallons float,
   mpg float
);

The following was tested in MySQL 5.5.14, but should work on other versions as well.

Let’s create our trigger. The key things to note here are BEFORE INSERT, since we want to insert the mpg value along with the miles and gallons, and NEW, since we are referring to a column of a new row to be inserted.

-- MySQL
CREATE TRIGGER trig__mileage BEFORE INSERT ON mileage FOR EACH ROW SET NEW.mpg  = NEW.miles / NEW.gallons;

Now with our trigger created, we can test to see if it fires correctly. If it fires correctly, we can expect our mpg column to be populated, otherwise it will be NULL.

-- MySQL
INSERT INTO mileage (miles, gallons) VALUES ( 250, 10 )

SELECT * FROM mileage;
+-------+---------+------+
| miles | gallons | mpg  |
+-------+---------+------+
|   250 |      10 |   25 |
+-------+---------+------+

Success! It really is that simple. Additional documentation, including more complex examples, can be found here.

Now on to PostgreSQL. These were tested on PostgreSQL 8.3.15, but should work on other versions as well.

The first thing to note is the CREATE TRIGGER statement in PostgreSQL takes a user supplied function as an argument. This function must return type TRIGGER, and in our instance will be responsible for calculating our miles per gallon.
In our function we use the NEW variable, which holds the new database row for insert.

-- PostgreSQL
CREATE OR REPLACE FUNCTION trig_proc__calculate_mpg() RETURNS TRIGGER AS $_$
BEGIN
   NEW.mpg := NEW.miles / NEW.gallons;
   RETURN NEW;
END $_$
LANGUAGE plpgsql;

With our function created, we now create the trigger.

-- PostgreSQL
CREATE TRIGGER trig__mileage BEFORE INSERT ON mileage FOR EACH ROW EXECUTE PROCEDURE trig_proc__calculate_mpg();

And now to test if our trigger fires correctly.

-- PostgreSQL
INSERT INTO mileage (miles, gallons) VALUES ( 312.3, 12.1 );

SELECT * FROM mileage;
 miles | gallons |       mpg
-------+---------+------------------
 312.3 |    12.1 | 25.8099173553719

As you can see, our trigger has populated the mpg column. Additional documentation on triggers in Postgres can be found here and here.