Triggers and MySQL.

Counters using triggers.

Triggers are a great way to gateher statistics and have 'running progress counters'.
So here is how it works.
Say you've got a table where you insert a record for every event, say start and end of a SIP-call.

CREATE TABLE Events(
	id	bigint,
	eventtype	ENUM('start','end'),
	eventtime	timestamp,
	PRIMARY KEY(id)
);

In order to do a 'running call counter' we could define the following sequence:
1. when a 'start'-event is inserted increase the counter by 1.
2. when an 'end'-event is inserted decrease the counter by 1.

So let's first create a table with 2 counters; a 'current calls'-counter and a 'total calls'-counter:

CREATE TABLE Event_counters(
	id bigint,
	name varchar(255),
	value bigint
	);
INSERT INTO Event_counters values(1,'Current calls',0);
INSERT INTO Event_counters values(2,'Total calls',0);

So after every insert in the Events-table we want to update the Event-counters.

DELIMITER |
CREATE TRIGGER after_insert
AFTER INSERT
ON Events
FOR EACH ROW BEGIN
	IF (NEW.eventtype='start') THEN
		UPDATE Event_counters SET value = value + 1 WHERE name='Current calls';
		UPDATE Event_counters SET value = value + 1 WHERE name='Total calls';
	END IF;
	IF (NEW.eventtype='end') THEN
		UPDATE Event_counters SET value = value - 1 WHERE name='Current calls';
	END IF;
END;|
DELIMITER ;

So let's test this:

mysql> select * from Event_counters;
+------+---------------+-------+
| id   | name          | value |
+------+---------------+-------+
|    1 | Current calls |     0 |
|    2 | Total calls   |     0 |
+------+---------------+-------+
2 rows in set (0.00 sec)

mysql> insert into Events values (1,'start',now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from Event_counters;
+------+---------------+-------+
| id   | name          | value |
+------+---------------+-------+
|    1 | Current calls |     1 |
|    2 | Total calls   |     1 |
+------+---------------+-------+
2 rows in set (0.00 sec)

It seems that the 'start'-event does the right thing. Now lets's try the 'end'-event:

mysql> insert into Events values (2,'end',now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from Event_counters;
+------+---------------+-------+
| id   | name          | value |
+------+---------------+-------+
|    1 | Current calls |     0 |
|    2 | Total calls   |     1 |
+------+---------------+-------+
2 rows in set (0.00 sec)

That also works!