Triggers Use Case Compilation, Part I

January 5, 2009

I've run by quite a few triggers lately on production systems. In previous posts, I've written about problems solved with triggers. So here's a compilation of some solutions based on triggers; and some problems which are not (yet?) solvable due to current triggers limitations.

Triggers can be used to:

  • Maintain integrity
  • Enhance security
  • Enhance logging
  • Assist with archiving
  • Restrict table size
  • Manage caching
  • Manage counters

Triggers are not fast. In fact, they can add quite an overhead if misused. Some of the triggers presented here are known to work on real life production systems, though, and work well. But make sure you benchmark before embarking on extensive application changes.

I'll be using MySQL's world database in some of the examples.

Maintaining value integrity

MySQL can enforce (is you're using the right sql_mode), some of the values you set for a column. For example, you should not be allowed to set a TINYINT column value to 500. You may not be allowed to set NULL, or you may have to provide default values.

However, within allowed range, SQL or MySQL in general won't help you. Assume you have a "percent" column, which holds integer values 0..100. It would be a TINYINT, of course. But setting the value to 103 is perfectly valid in MySQL's point of view, though not so in yours.

This is where triggers come in handy. With a trigger, you may truncate illegal values or completely abort the operation if something doesn't seem right. For example, we may wish to enforce a city's district to be non-empty. We may also wish to ensure that the city's population does not exceed its country's population:

DELIMITER $$
DROP TRIGGER IF EXISTS City_bu $$
CREATE TRIGGER City_bu BEFORE UPDATE ON City
FOR EACH ROW
BEGIN
  DECLARE country_population INT;

  IF (CHAR_LENGTH(NEW.District) = 0) THEN
    SELECT 0 FROM `District must not be empty` INTO @error;
  END IF;

  SELECT MAX(Population) FROM Country
    WHERE Code = NEW.CountryCode INTO country_population;
  IF (NEW.Population > country_population) THEN
    SELECT 0 FROM `City population cannot exceed that of country!` INTO @error;
  END IF;
END $$
DELIMITER ;

For example:

mysql> UPDATE City SET Population=100000000 WHERE Name='London';
ERROR 1146 (42S02): Table 'world.City population cannot exceed that of country!' doesn't exist

We force the trigger to fail under certain circumstances. Since this is a BEFORE INSERT trigger, failure of the trigger causes aborting the INSERT itself.

Forcing referential integrity

If you're using MyISAM, Memory or even Maria or Falcon, you don't get to use Foreign Keys. MySQL's plan is to add foreign keys for all storage engines. The plan is on print for quite a few years now. Till then, you may use triggers to simulate foreign keys, including cascading deletes and updates.

Let's consider the tables City and Country. If we could, we would add the contraint that City.CountryCode references Country.Code. How can this be achieved with triggers? Here's a partial solution, showing a DELETE CASCADE:

DELIMITER $$

DROP TRIGGER IF EXISTS City_bi $$
CREATE TRIGGER City_bi BEFORE INSERT ON City
FOR EACH ROW
BEGIN
  IF (NOT EXISTS (SELECT NULL FROM Country WHERE Code=NEW.CountryCode)) THEN
    SELECT 0 FROM `CountryCode does not exist in Country table` INTO @error;
  END IF;
END $$

DROP TRIGGER IF EXISTS Country_ad $$
CREATE TRIGGER Country_ad AFTER DELETE ON Country
FOR EACH ROW
BEGIN
  DELETE FROM City WHERE CountryCode = OLD.Code;
END $$

DELIMITER ;

Trying out some queries:

mysql> INSERT INTO City (Name, CountryCode) VALUES ('zzimbwawa', 'ZWZ');
ERROR 1146 (42S02): Table 'world.CountryCode does not exist in Country table' doesn't exist

mysql> INSERT INTO City (Name, CountryCode) VALUES ('zzimbwawa', 'GBR');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT COUNT(*) FROM City WHERE CountryCode = 'GBR';
+----------+
| COUNT(*) |
+----------+
|       82 |
+----------+
1 row in set (0.01 sec)

mysql> DELETE FROM Country WHERE Code='GBR';
Query OK, 1 row affected (0.04 sec)

mysql> SELECT COUNT(*) FROM City WHERE CountryCode = 'GBR';
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

The above example is partial. It does not handle UPDATEs on both tables. You may also modify it to simulate ON DELETE SET NULL instead of ON DELETE CASCADE.

Maintaining denormalized data integrity

Denormalized tables can hold data duplicated in several places. When such data changes in one place, triggers can help out with updating the change in the rest occurrences. A post was recently written which discusses this issue.

Archiving

Assume the following table:

DROP TABLE IF EXISTS `logs`;
CREATE TABLE  `logs` (
  `id` int(11) NOT NULL auto_increment,
  `subject` varchar(64) NOT NULL,
  `message` varchar(255) NOT NULL,
  `severity` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`)
);

Logs are something that you want to cleanup regularly, on one hand, but keep at a safe place on the other hand. Let's create a logs_archive table:

CREATE TABLE logs_archive LIKE logs;

We can automatically move records from the logs table to the logs_archive table:

DELIMITER $$
DROP TRIGGER IF EXISTS logs_bd $$
CREATE TRIGGER logs_bd BEFORE DELETE ON logs
FOR EACH ROW
BEGIN
  INSERT INTO logs_archive SELECT * FROM logs WHERE id=OLd.id;
END $$
DELIMITER ;

Example:

mysql> INSERT INTO logs (subject, message) VALUES ('info', 'new user created');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO logs (subject, message) VALUES ('info', 'cleanup completed');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM logs;
+----+---------+-------------------+----------+
| id | subject | message           | severity |
+----+---------+-------------------+----------+
|  1 | info    | new user created  |        0 |
|  2 | info    | cleanup completed |        0 |
+----+---------+-------------------+----------+
2 rows in set (0.00 sec)

mysql> DELETE FROM logs WHERE id = 1;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM logs;
+----+---------+-------------------+----------+
| id | subject | message           | severity |
+----+---------+-------------------+----------+
|  2 | info    | cleanup completed |        0 |
+----+---------+-------------------+----------+
1 row in set (0.01 sec)

mysql> SELECT * FROM logs_archive;
+----+---------+------------------+----------+
| id | subject | message          | severity |
+----+---------+------------------+----------+
|  1 | info    | new user created |        0 |
+----+---------+------------------+----------+
1 row in set (0.00 sec)

We can see that the logs_archive table has been filled with rows deleted from logs table.

Logging

Triggers can be used to automatically log significant events. As an example, let's say I have a social network application, in which an 'online_user' table lists those users which have logged in and have not yet logged out (hence they are assumed to be online):

DROP TABLE IF EXISTS `online_user`;
CREATE TABLE `online_user` (
  `online_user_id` int(11) NOT NULL auto_increment,
  `login` VARCHAR(64) CHARSET ascii NOT NULL,
  `ipv4` INT UNSIGNED NOT NULL,
  `ts` TIMESTAMP,
  PRIMARY KEY  (`online_user_id`)
);

Our application knows how to handle this table. I can enhance my database with logging by adding a logs table, and additional triggers:

DROP TABLE IF EXISTS `logs`;
CREATE TABLE `logs` (
  `logs_id` int(11) NOT NULL auto_increment,
  `ts` TIMESTAMP,
  `message` VARCHAR(255) CHARSET utf8 NOT NULL,
  PRIMARY KEY  (`logs_id`)
);

DELIMITER $$

DROP TRIGGER IF EXISTS online_user_ai $$
CREATE TRIGGER online_user_ai AFTER INSERT ON online_user
FOR EACH ROW
BEGIN
  INSERT INTO logs (message) VALUES (CONCAT('User ',NEW.login, ' has logged in from ', INET_NTOA(NEW.ipv4)));
END $$

DROP TRIGGER IF EXISTS online_user_ad $$
CREATE TRIGGER online_user_ad AFTER DELETE ON online_user
FOR EACH ROW
BEGIN
  INSERT INTO logs (message) VALUES (CONCAT('User ',OLD.login, ' has logged out'));
END $$

DELIMITER ;

Let's see the effect of managing online users:

INSERT INTO online_user (login, ipv4) VALUES ('john', 123456);
INSERT INTO online_user (login, ipv4) VALUES ('mark', 654321);
SELECT SLEEP(12);
DELETE FROM online_user WHERE login = 'john';

Checking up on the logs table, we get:

mysql> SELECT * FROM `logs`;
+---------+---------------------+------------------------------------------+
| logs_id | ts                  | message                                  |
+---------+---------------------+------------------------------------------+
|       1 | 2008-12-22 11:16:31 | User john has logged in from 0.1.226.64  |
|       2 | 2008-12-22 11:16:31 | User mark has logged in from 0.9.251.241 |
|       3 | 2008-12-22 11:16:43 | User john has logged out                 |
+---------+---------------------+------------------------------------------+
3 rows in set (0.00 sec)

The logs table can be used for logging any change in any table. The application need not be aware what exactly is being logged.

If the logs table uses the MyISAM storage engine, the triggers may want to replace the INSERT with an INSERT DELAYED, so that they return immediately without waiting for locks on the logs table. Assuming no crash occurs right after, a separate thread will collect all inserts on the logs table, and handle them in its own free time.

More to come

More triggers use case, as well as limitations and workarounds, will be presented in following posts.

Triggers Use Case Compilation, Part II

tags: ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

18 Comments to "Triggers Use Case Compilation, Part I"

  1. Roland Bouman wrote:

    Hi! Nice post again ;)

    "Till then, you may use triggers to simulate foreign keys, including cascading deletes and updates."

    I have a script to generate code for that. You run it on an innodb schema, and it outputs the trigger code. (Run the script with mysql command line client using -Nrs argument to get clean code output)

    http://rpbouman.blogspot.com/2008/04/mysql-uc2008-presentation-grand-tour-of.html

    There some more useful stuff in there, I hope you like it.

    Regards,

    Roland Bouman

  2. shlomi wrote:

    Hi Roland,

    Indeed, very useful stuff. I'll take note to check up on your scripts and utilities. There's many of them, and it's hard to keep up!

    Shlomi

  3. Mark Callaghan wrote:

    It is not easy to implement referential integrity with triggers on storage engines that allow concurrent transactions. The trigger that confirms that the referenced Country exists on inserts to City doesn't work for InnoDB and might not work for Maria and Falcon. The fix in this case is to change the SELECT to a SELECT ... LOCK IN SHARE MODE.

    And given the lack of transactons with MyISAM, is there really a point in trying to enforce referential integrity?

  4. shlomi wrote:

    Hi Mark,

    Thanks. You are right. Using triggers on MyISAM (and I really don't know yet what of Maria and Falcon) does not provide you with real integrity. Using LOCK IN SHARE MODE on InnoDB will probably cost a lot.

    Nevertheless I still find the DELETE CASCASE simulation to be helpful: it helps in taking out the garbage (orphaned rows).
    Otherwise, and with no real FK, cascading deletes must be performed on the application level, or by periodic pruning.

  5. Nicklas Westerlund wrote:

    Don't forget that you can use triggers in a replicated environment in order to mask real values in a dev environment for example as well.

  6. shlomi wrote:

    Hi Nicklas,

    Can you please elaborate on this?
    Regards

    PS, there are more parts to this post

  7. code.openark.org » Blog Archive » Triggers Use Case Compilation, Part II wrote:

    [...] Triggers Use Case Compilation, Part II [...]

  8. Roland Bouman wrote:

    Hi!

    "An interesting point, though, is exposed here: trigger-based integrity can work cross-schemata, whereas InnoDB’s foreign keys only work withing a given schema."

    I don't think this is true.

    mysql> use t1
    Database changed
    mysql> create table t1 (id int primary key);
    Query OK, 0 rows affected (0.23 sec)

    mysql> create database t2;
    Query OK, 1 row affected (0.00 sec)

    mysql> create table t2 (id int, foreign key(id) references t1.t1 (id));
    Query OK, 0 rows affected (0.11 sec)

    mysql> insert into t2 values (1);
    ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`t1`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`))

    mysql> insert into t1.t1 values (1);
    Query OK, 1 row affected (0.13 sec)

    mysql> insert into t2 values (1);
    Query OK, 1 row affected (0.09 sec)

    mysql> delete from t1.t1;
    ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`t1`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`))

    So, it works for me.

  9. Roland Bouman wrote:

    Hi! sorry - there should be a:

    USE t2

    after the

    create database t2;

    But, it works across schemata nonetheless....

  10. shlomi wrote:

    Roland,

    thanks for this important correction. I've updated the text. I have to check how I came to that conclusion.

    In your sample code above, it is best if you specified ENGINE=InnoDB for those who have MyISAM as default storage engine.

    When doing the above with MyISAM, no error is ever produced. Moreover, trying to ALTER tables to InnoDB still works fine, since the foreign key was silently dropped as we were using MyISAM. Will check if there's an open bug on this.

    Regards,
    Shlomi

  11. Triggers Use Case Compilation, Part III | code.openark.org wrote:

    [...] Triggers Use Case Compilation, Part III [...]

  12. Samuel wrote:

    Thank you for your generous contribution.

    I am in the process of moving a large very specialized linguistic database in MS SQL to MySQL (way cheaper on aws.amazon). Referential integrity was enforced in SQL Server using triggrs that checked the inserted values against views -- this was the ONLY way possible given the highly customized way words in this database are categorized (over 400 unique lexical senses). I was beating my head bloody trying to figure out a way to "trick" MySQL into similar functionality, and happily like the song says "then came you!"

    Again, thank you for your generosity sharing your knowledge.

  13. shlomi wrote:

    @Samuel,

    With pleasure. Do take notice that triggers will *considerably* slow down your queries. If this is a one time job, for importing data from MSSQL, this may be fine; but make sure to check on your performance if you're going live with active triggers.

  14. Samuel wrote:

    @Shlomi,

    Kindly appreciate the notice.

    The triggers are for insert and update on the reference tables -- the master dictionary if you will. Trigger firings, therefore, are extremely rare -- the frequency of "discovering" a new word to add. These reference tables are virtually read only.

    The more active write tables are strictly no-frills word-usage data collection. No triggers involved.

  15. SQL: forcing single row tables integrity | code.openark.org wrote:

    [...] Triggers Use Case Compilation, Part I [...]

  16. When are MySQL Triggers not a good idea? | DIGG LINK wrote:

    [...] read the docs, FAQs, Forum and other sites and witnessed plenty of use cases, but haven’t come across a discussion of best [...]

  17. Impact of foreign keys absence on replicating slaves | code.openark.org wrote:

    [...] rely on integrity with cascading constraints. An ugly patch might be to use triggers so as to simulate their behavior. Performance wise this is very bad. [...]

  18. Eswar wrote:

    can we use when clause in mysql triggers?? if Yes, please give me an example.

Leave Your Comment

 

 
Powered by Wordpress and MySQL. Theme by openark.org