Skip to content

# Triggers Use Case Compilation, Part I

MySQL Security Triggers January 5, 2009January 16, 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

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
19
Leave a Reply

19 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
6 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Roland Bouman

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

Author
shlomi

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

Guest
Mark Callaghan

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?

Author
shlomi

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.

Guest
Nicklas Westerlund

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.

Author
shlomi

Hi Nicklas,

Can you please elaborate on this?
Regards

PS, there are more parts to this post

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

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

Guest
Roland Bouman

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`,… Read more »

Guest
Roland Bouman

Hi! sorry – there should be a:

USE t2

after the

create database t2;

But, it works across schemata nonetheless….

Author
shlomi

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

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

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

Guest
Samuel

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… Read more »

Author
shlomi

@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.

Guest
Samuel

@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.

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

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

When are MySQL Triggers not a good idea? | DIGG LINK

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

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

[…] 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. […]

Guest
Eswar

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

MySQL: Delete a record from all tables in one shot | XL-UAT

[…] keys at the moment, otherwise you wouldn’t ask. In which case you may try using triggers, see this post for an example. Triggers are bad for performance, mind […]

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.