How To Automate Your Database Using Triggers and Events

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:

  1. 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.
  2. Triggers are not a substitute for transactions. Trigger code can fail so you should still wrap related updates in a single transaction.
  3. 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.
  4. 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.
  5. 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:

Win an Annual Membership to Learnable,

SitePoint's Learning Platform

  • http://www.itmitica.com/en IT Mitică

    Triggers are useful, when used for the right purpose.

    I don’t rely on triggers much but I’m particularly fond of the INSTEAD OF trigger. It’s an elegant way to bring views to life, offering something similar to interface classes.

  • http://www.facebook.com/chaz.scholton Chaz Scholton

    Craig, this is a great heads up article for SQL novices. My strength is with working MS SQL Servers, the concept of Events or Scheduled jobs is very powerful. I tend to automated so much of the routine maintenance tasks. Be it operations to optimize database tables, back-ups, clean up old table data and you name it. I think the most important thing to mention is to not rely upon Events to Become Band-Aid fixes for Data integrity issues. I’ve encountered automated event tasks cleaning up after bad code. On the other side of the Equation, I’ve never been very Trigger happy. I have resorted to using Triggers on a temporary basis while re-coding or fixing an application. I tend to not favor use of Triggers because of the performance hits involved. Most cases Triggers can be totally avoided if things are well thought out. Even exploring other alternatives. (just my two cents worth).

  • http://www.drlinux.no/ drlinux

    Nice article!

    I have avoided triggers for fear of adding complexity and duplicating effort, but your example with the audit-log is very nice. I have been doing that with a log() method in the back-end code, but might have to try doing it with triggers.

    Regarding events, do you consider it safer or better performance wise to use events rather that regular cron-jobs?

    Also, will events and triggers be dumped with the schema and data if using mysqldump? If not, it will be hard to use it for projects where you need to send the database to a client.

  • Ndawula Roger

    Thts greate but i need help. I want help about how i can trigger my mysql table called bank_ledger in that when on insert data from my form called payable, the trigger helps me in sorting the bank_ledger data by arranging inserted amounts as per cheque date even though the cheque date is not current whereby the outstanding balance of the table bank_ledger is well reconciled e.g like a bank statement. Please help.