Databases are incredibly powerful. Unfortunately, they have a reputation for being complex and mysterious beasts ruled by dark overlords who speak in strange tongues. I’m guilty of developing web applications which shunned great database techniques I didn’t understand. I don’t think I’m alone — many developers prefer to re-invent wheels in PHP rather than implement database functionality which could save time and effort while increasing performance.
Today, we’re going to look at triggers and events — features supported by most popular commercial and open source database systems…
Triggers
A trigger is code run just before or just after an INSERT, UPDATE or DELETE SQL event occurs on a particular database table. That code can verify or modify the incoming data, perform calculations, run further SQL commands, etc.
Triggers are supported in MySQL, PostgreSQL, SQLite, Firebird, DB2, Microsoft SQL Server and Oracle. Implementation varies so you should check the appropriate documentation before coding.
Events (or Temporal Triggers)
Events are often referred to as “temporal triggers” because they’re scheduled by time rather than a table update. Events can scheduled to run once or more during specific periods. Effectively, they’re database-only cron jobs and could be used to archive data, clean logs, or calculate information for complex reports.
Fewer databases support events but most provide a similar solution. Not all will be easy to implement within a typical web application so please check your documentation.
When Should You Use Triggers?
Triggers can be as simple or complex as you like. That said, there are a few situations where they should be avoided:
- Triggers should not be used in place of foreign key constraints. Foreign keys enforce referential integrity so it becomes impossible to add, remove or edit data which would leave orphan records. For example, if you delete a user from your social networking system, you don’t necessarily want messages from that person to be retained.
- Triggers are not a substitute for transactions. Trigger code can fail so you should still wrap related updates in a single transaction.
- Be wary about splitting your business logic between the database and your back-end systems (written in PHP, C#, Java etc). Maintenance will become more difficult if your code is separated.
- Avoid duplication of effort. Your back-end code should be sanitizing user input so it should not be necessary to repeat those checks within the database.
- Triggers incur a performance overhead. They will execute quicker than a series of SQL commands passed by back-end code, but you should refrain from using complex triggers on regularly modified tables.
Ideally, triggers should be considered when they automate changes which are specific to the database or management of its data. An audit log is a good example. Consider a WordPress-like CMS system with a ‘blog’ table containing titles and body content. An ‘audit’ table could record the date and time an article is added, edited or removed. Your web system may never present that information or even know it’s recorded so a trigger would be ideal.
Our ‘blog’ table would have a one-to-many relationship with the ‘audit’ table; in other words, one or more audit records would point at a single post. Assume our system wants to retrieve the title, body content and last update date, i.e. the post itself and its last recorded entry in the ‘audit’ table. We can obtain that information but the SELECT command is complex and requires sub-selects to ensure we only retrieve the last audit record.
A trigger could help us reduce the complexity and increase performance. For example, we could add a ‘last_audit_id’ field to our ‘blog’ table. Whenever a post is updated, a new entry would be added to the ‘audit’ table and its ID would be recorded in blog.last_audit_id.
When Should You Use Events?
Scheduled events are ideal for batch processing large volumes of data during off-peak periods.
Assume we want to delete a post from our ‘blog’ table. Rather than executing a SQL DELETE, we would set a boolean ‘deleted’ field to true so that post can be undeleted. After a while, our table will become larger, slower and perhaps contain a large proportion of deleted articles. To solve this problem, a scheduled event could be run once a week to move older deleted posts and audit records into archive tables. It could also physically delete a post once it reaches a specific age.
These are only simple suggestions and your application will have different requirements. Fortunately, triggers and events are supported in the web’s most-used relational database system: MySQL 5.x. In my following articles, we’ll discuss how to create a similar blog database to that described above.
See also:
- Top 10 MySQL Mistakes Made By PHP Developers
- How to Use MySQL Foreign Keys for Quicker Database Development
- MySQL Transactions Why They Can’t Be Emulated in PHP
