MySQL – 5.5 Signals

Signals have been introduced in MySQL 5.5. which allow exceptions to be raised. This was previously unavailable and has made it difficult to write stored solid stored procedures with exception handling. This post will show a trigger implementing data consistency checks and raising an exception. This is particularly good for implementing check constraints across multiple tables.

The scenario is that we have a database that contains numerous tables video, audio and pdf and inside each of these tables we have a field called resource name. The resource name must be unique in the database irrespective of the database. I know that this is not a good practice but this is just an example of how SIGNAL’s may be used in a trigger to determine the uniqueness of the resource name.

CREATE TABLE audio (id int auto_increment, resource_name varchar(255), ...., primary key (id));
CREATE TABLE video (id int auto_increment, resource_name varchar(255), ...., primary key (id));
CREATE TABLE pdf (id int auto_increment, resource_name varchar(255), ...., primary key (id));

So far example we need to verify when something is added in audio that no similar resource_name exists in any other of the tables. We are assuming sequential input from a single source.

DELIMITER $$
CREATE TRIGGER audio01
AFTER INSERT ON audio
FOR EACH ROW BEGIN
  DECLARE count INT;
  SELECT COUNT(1) INTO count FROM video WHERE resource_name = new.resource_name;
  IF count > 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Resource name not unique';
  END IF;
  SELECT COUNT(1) INTO count FROM pdf WHERE resource_name = new.resource_name;
  IF count > 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Resource name not unique';
  END IF;
END;
$$
DELIMITER ;

The result is we can now build solid applications with more complex data integrity constraints and improved exception handling.

Here is the code in action:

mysql> insert into audio(resource_name) values ('test');
Query OK, 1 row affected (0.02 sec)

mysql> select * from audio;
+----+---------------+
| id | resource_name |
+----+---------------+
|  1 | test          |
+----+---------------+
1 row in set (0.00 sec)

mysql> insert into video(resource_name) values ('test1');
Query OK, 1 row affected (0.06 sec)

mysql> insert into audio(resource_name) values ('test1');
ERROR 1644 (45000): Resource name not unique
mysql> select * from audio;
+----+---------------+
| id | resource_name |
+----+---------------+
|  1 | test          |
+----+---------------+
1 row in set (0.00 sec)