{"id":313,"date":"2009-01-05T11:55:15","date_gmt":"2009-01-05T09:55:15","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=313"},"modified":"2009-01-16T14:06:42","modified_gmt":"2009-01-16T12:06:42","slug":"triggers-use-case-compilation-part-i","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/triggers-use-case-compilation-part-i","title":{"rendered":"Triggers Use Case Compilation, Part I"},"content":{"rendered":"<p>I&#8217;ve run by quite a few triggers lately on production systems. In previous posts, I&#8217;ve written about problems solved with triggers. So here&#8217;s a compilation of some solutions based on triggers; and some problems which are not (yet?) solvable due to current triggers limitations.<\/p>\n<p>Triggers can be used to:<\/p>\n<ul>\n<li>Maintain integrity<\/li>\n<li>Enhance security<\/li>\n<li>Enhance logging<\/li>\n<li>Assist with archiving<\/li>\n<li>Restrict table size<\/li>\n<li>Manage caching<\/li>\n<li>Manage counters<\/li>\n<\/ul>\n<p>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.<!--more--><\/p>\n<p>I&#8217;ll be using <a title=\"MySQL's world database setup\" href=\"http:\/\/dev.mysql.com\/doc\/world-setup\/en\/world-setup.html\">MySQL&#8217;s world database<\/a> in some of the examples.<\/p>\n<h4>Maintaining value integrity<\/h4>\n<p>MySQL can enforce (is you&#8217;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.<\/p>\n<p>However, within allowed range, SQL or MySQL in general won&#8217;t help you. Assume you have a &#8220;percent&#8221; 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&#8217;s point of view, though not so in yours.<\/p>\n<p>This is where triggers come in handy. With a trigger, you may truncate illegal values or completely abort the operation if something doesn&#8217;t seem right. For example, we may wish to enforce a city&#8217;s district to be non-empty. We may also wish to ensure that the city&#8217;s population does not exceed its country&#8217;s population:<\/p>\n<blockquote>\n<pre>DELIMITER $$\r\nDROP TRIGGER IF EXISTS City_bu $$\r\nCREATE TRIGGER City_bu BEFORE UPDATE ON City\r\nFOR EACH ROW\r\nBEGIN\r\n  DECLARE country_population INT;\r\n\r\n  IF (CHAR_LENGTH(NEW.District) = 0) THEN\r\n    SELECT 0 FROM `District must not be empty` INTO @error;\r\n  END IF;\r\n\r\n  SELECT MAX(Population) FROM Country\r\n    WHERE Code = NEW.CountryCode INTO country_population;\r\n  IF (NEW.Population &gt; country_population) THEN\r\n    SELECT 0 FROM `City population cannot exceed that of country!` INTO @error;\r\n  END IF;\r\nEND $$\r\nDELIMITER ;<\/pre>\n<\/blockquote>\n<p>For example:<\/p>\n<blockquote>\n<pre>mysql&gt; UPDATE City SET Population=100000000 WHERE Name='London';\r\n<span style=\"color: #993300;\">ERROR 1146 (42S02): Table 'world.City population cannot exceed that of country!' doesn't exist<\/span><\/pre>\n<\/blockquote>\n<p>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.<\/p>\n<h4>Forcing referential integrity<\/h4>\n<p>If you&#8217;re using MyISAM, Memory or even Maria or Falcon, you don&#8217;t get to use Foreign Keys. MySQL&#8217;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.<\/p>\n<p>Let&#8217;s consider the tables <strong>City<\/strong> and <strong>Country<\/strong>. If we could, we would add the contraint that <strong>City.CountryCode<\/strong> references <strong>Country.Code<\/strong>. How can this be achieved with triggers? Here&#8217;s a partial solution, showing a DELETE CASCADE:<\/p>\n<blockquote>\n<pre>DELIMITER $$\r\n\r\nDROP TRIGGER IF EXISTS City_bi $$\r\nCREATE TRIGGER City_bi BEFORE INSERT ON City\r\nFOR EACH ROW\r\nBEGIN\r\n  IF (NOT EXISTS (SELECT NULL FROM Country WHERE Code=NEW.CountryCode)) THEN\r\n    SELECT 0 FROM `CountryCode does not exist in Country table` INTO @error;\r\n  END IF;\r\nEND $$\r\n\r\nDROP TRIGGER IF EXISTS Country_ad $$\r\nCREATE TRIGGER Country_ad AFTER DELETE ON Country\r\nFOR EACH ROW\r\nBEGIN\r\n  DELETE FROM City WHERE CountryCode = OLD.Code;\r\nEND $$\r\n\r\nDELIMITER ;<\/pre>\n<\/blockquote>\n<p>Trying out some queries:<\/p>\n<blockquote>\n<pre>mysql&gt; INSERT INTO City (Name, CountryCode) VALUES ('zzimbwawa', 'ZWZ');\r\n<span style=\"color: #993300;\">ERROR 1146 (42S02): Table 'world.CountryCode does not exist in Country table' doesn't exist\r\n<\/span>\r\nmysql&gt; INSERT INTO City (Name, CountryCode) VALUES ('zzimbwawa', 'GBR');\r\nQuery OK, 1 row affected (0.00 sec)\r\n\r\nmysql&gt; SELECT COUNT(*) FROM City WHERE CountryCode = 'GBR';\r\n+----------+\r\n| COUNT(*) |\r\n+----------+\r\n|       82 |\r\n+----------+\r\n1 row in set (0.01 sec)\r\n\r\nmysql&gt; DELETE FROM Country WHERE Code='GBR';\r\nQuery OK, 1 row affected (0.04 sec)\r\n\r\nmysql&gt; SELECT COUNT(*) FROM City WHERE CountryCode = 'GBR';\r\n+----------+\r\n| COUNT(*) |\r\n+----------+\r\n|        0 |\r\n+----------+\r\n1 row in set (0.00 sec)<\/pre>\n<\/blockquote>\n<p>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.<\/p>\n<h4>Maintaining denormalized data integrity<\/h4>\n<p>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 <a href=\"http:\/\/karlssonondatabases.blogspot.com\/2008\/12\/using-triggers-for-performance.html\">post<\/a> was recently written which discusses this issue.<\/p>\n<h4>Archiving<\/h4>\n<p>Assume the following table:<\/p>\n<blockquote>\n<pre>DROP TABLE IF EXISTS `logs`;\r\nCREATE TABLE  `logs` (\r\n  `id` int(11) NOT NULL auto_increment,\r\n  `subject` varchar(64) NOT NULL,\r\n  `message` varchar(255) NOT NULL,\r\n  `severity` tinyint(4) NOT NULL default '0',\r\n  PRIMARY KEY  (`id`)\r\n);<\/pre>\n<\/blockquote>\n<p>Logs are something that you want to cleanup regularly, on one hand, but keep at a safe place on the other hand. Let&#8217;s create a <strong>logs_archive<\/strong> table:<\/p>\n<blockquote>\n<pre>CREATE TABLE logs_archive LIKE logs;<\/pre>\n<\/blockquote>\n<p>We can automatically move records from the logs table to the logs_archive table:<\/p>\n<blockquote>\n<pre>DELIMITER $$\r\nDROP TRIGGER IF EXISTS logs_bd $$\r\nCREATE TRIGGER logs_bd BEFORE DELETE ON logs\r\nFOR EACH ROW\r\nBEGIN\r\n  INSERT INTO logs_archive SELECT * FROM logs WHERE id=OLd.id;\r\nEND $$\r\nDELIMITER ;<\/pre>\n<\/blockquote>\n<p>Example:<\/p>\n<blockquote>\n<pre>mysql&gt; INSERT INTO logs (subject, message) VALUES ('info', 'new user created');\r\nQuery OK, 1 row affected (0.00 sec)\r\n\r\nmysql&gt; INSERT INTO logs (subject, message) VALUES ('info', 'cleanup completed');\r\nQuery OK, 1 row affected (0.00 sec)\r\n\r\nmysql&gt; SELECT * FROM logs;\r\n+----+---------+-------------------+----------+\r\n| id | subject | message           | severity |\r\n+----+---------+-------------------+----------+\r\n|  1 | info    | new user created  |        0 |\r\n|  2 | info    | cleanup completed |        0 |\r\n+----+---------+-------------------+----------+\r\n2 rows in set (0.00 sec)\r\n\r\nmysql&gt; DELETE FROM logs WHERE id = 1;\r\nQuery OK, 1 row affected (0.01 sec)\r\n\r\nmysql&gt; SELECT * FROM logs;\r\n+----+---------+-------------------+----------+\r\n| id | subject | message           | severity |\r\n+----+---------+-------------------+----------+\r\n|  2 | info    | cleanup completed |        0 |\r\n+----+---------+-------------------+----------+\r\n1 row in set (0.01 sec)\r\n\r\nmysql&gt; SELECT * FROM logs_archive;\r\n+----+---------+------------------+----------+\r\n| id | subject | message          | severity |\r\n+----+---------+------------------+----------+\r\n|  1 | info    | new user created |        0 |\r\n+----+---------+------------------+----------+\r\n1 row in set (0.00 sec)<\/pre>\n<\/blockquote>\n<p>We can see that the <strong>logs_archive<\/strong> table has been filled with rows deleted from <strong>logs<\/strong> table.<\/p>\n<h4>Logging<\/h4>\n<p>Triggers can be used to automatically log significant events. As an example, let&#8217;s say I have a social network application, in which an &#8216;online_user&#8217; table lists those users which have logged in and have not yet logged out (hence they are assumed to be online):<\/p>\n<blockquote>\n<pre>DROP TABLE IF EXISTS `online_user`;\r\nCREATE TABLE `online_user` (\r\n  `online_user_id` int(11) NOT NULL auto_increment,\r\n  `login` VARCHAR(64) CHARSET ascii NOT NULL,\r\n  `ipv4` INT UNSIGNED NOT NULL,\r\n  `ts` TIMESTAMP,\r\n  PRIMARY KEY  (`online_user_id`)\r\n);<\/pre>\n<\/blockquote>\n<p>Our application knows how to handle this table. I can enhance my database with logging by adding a logs table, and additional triggers:<\/p>\n<blockquote>\n<pre>DROP TABLE IF EXISTS `logs`;\r\nCREATE TABLE `logs` (\r\n  `logs_id` int(11) NOT NULL auto_increment,\r\n  `ts` TIMESTAMP,\r\n  `message` VARCHAR(255) CHARSET utf8 NOT NULL,\r\n  PRIMARY KEY  (`logs_id`)\r\n);\r\n\r\nDELIMITER $$\r\n\r\nDROP TRIGGER IF EXISTS online_user_ai $$\r\nCREATE TRIGGER online_user_ai AFTER INSERT ON online_user\r\nFOR EACH ROW\r\nBEGIN\r\n  INSERT INTO logs (message) VALUES (CONCAT('User ',NEW.login, ' has logged in from ', INET_NTOA(NEW.ipv4)));\r\nEND $$\r\n\r\nDROP TRIGGER IF EXISTS online_user_ad $$\r\nCREATE TRIGGER online_user_ad AFTER DELETE ON online_user\r\nFOR EACH ROW\r\nBEGIN\r\n  INSERT INTO logs (message) VALUES (CONCAT('User ',OLD.login, ' has logged out'));\r\nEND $$\r\n\r\nDELIMITER ;<\/pre>\n<\/blockquote>\n<p>Let&#8217;s see the effect of managing online users:<\/p>\n<blockquote>\n<pre>INSERT INTO online_user (login, ipv4) VALUES ('john', 123456);\r\nINSERT INTO online_user (login, ipv4) VALUES ('mark', 654321);\r\nSELECT SLEEP(12);\r\nDELETE FROM online_user WHERE login = 'john';<\/pre>\n<\/blockquote>\n<p>Checking up on the logs table, we get:<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT * FROM `logs`;\r\n+---------+---------------------+------------------------------------------+\r\n| logs_id | ts                  | message                                  |\r\n+---------+---------------------+------------------------------------------+\r\n|       1 | 2008-12-22 11:16:31 | User john has logged in from 0.1.226.64  |\r\n|       2 | 2008-12-22 11:16:31 | User mark has logged in from 0.9.251.241 |\r\n|       3 | 2008-12-22 11:16:43 | User john has logged out                 |\r\n+---------+---------------------+------------------------------------------+\r\n3 rows in set (0.00 sec)<\/pre>\n<\/blockquote>\n<p>The <strong>logs<\/strong> table can be used for logging any change in any table. The application need not be aware what exactly is being logged.<\/p>\n<p>If the <strong>logs<\/strong> table uses the MyISAM storage engine, the triggers may want to replace the <strong>INSERT<\/strong> with an <strong>INSERT DELAYED<\/strong>, so that they return immediately without waiting for locks on the <strong>logs<\/strong> table. Assuming no crash occurs right after, a separate thread will collect all inserts on the <strong>logs<\/strong> table, and handle them in its own free time.<\/p>\n<h4>More to come<\/h4>\n<p>More triggers use case, as well as limitations and workarounds, will be presented in following posts.<\/p>\n<p><a title=\"Triggers Use Case Compilation, Part II\" href=\"http:\/\/code.openark.org\/blog\/mysql\/triggers-use-case-compilation-part-ii\">Triggers Use Case Compilation, Part II<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;ve run by quite a few triggers lately on production systems. In previous posts, I&#8217;ve written about problems solved with triggers. So here&#8217;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 [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[16,30],"class_list":["post-313","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-security","tag-triggers"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-53","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/313","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=313"}],"version-history":[{"count":25,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/313\/revisions"}],"predecessor-version":[{"id":522,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/313\/revisions\/522"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=313"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=313"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=313"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}