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:

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:

Frequently Asked Questions (FAQs) about Database Triggers and Events

What is the main purpose of using database triggers in a system?

Database triggers are primarily used to maintain the integrity of the data in a database. They are special procedures that are automatically executed or fired when certain events occur like insertions, updates, or deletions in a database table. Triggers can help to automate certain tasks, enforce business rules, and control data validation, thus reducing the likelihood of errors and ensuring consistency in the data.

How do database triggers differ from stored procedures?

While both triggers and stored procedures are database objects that contain a block of code, their execution process differs. Stored procedures need to be explicitly called to execute, whereas triggers are automatically executed or fired in response to certain events in the database.

Can you provide an example of a database trigger?

Sure, here’s a simple example of a trigger in SQL. This trigger is designed to automatically update the ‘ModifiedDate’ field to the current date whenever a record in the ‘Employees’ table is updated.

CREATE TRIGGER trg_UpdateEmployeeDate
ON Employees
AFTER UPDATE
AS
BEGIN
UPDATE Employees
SET ModifiedDate = GETDATE()
WHERE EmployeeID = Inserted.EmployeeID
END

What are the different types of database triggers?

There are mainly two types of triggers: Row-level triggers and Statement-level triggers. Row-level triggers are fired each time a row is affected by a DML operation like INSERT, UPDATE, or DELETE. Statement-level triggers, on the other hand, are fired once for each transaction, regardless of how many rows are affected.

What are the potential drawbacks of using database triggers?

While triggers can be very useful, they can also lead to some issues if not used carefully. They can make debugging more difficult because they are not directly invoked. They can also lead to performance issues if they are not designed efficiently, as they are executed automatically whenever the triggering event occurs.

What is the difference between BEFORE and AFTER triggers?

BEFORE triggers are executed before the triggering SQL statement is executed, whereas AFTER triggers are executed after the triggering SQL statement is executed successfully. The choice between using a BEFORE or AFTER trigger depends on the specific requirements of the business logic.

Can a trigger access tables?

Yes, a trigger can access other tables. However, it’s important to be cautious when doing this to avoid potential issues such as mutating table errors.

What is a database event?

A database event is a specific condition or state change in a database that can be monitored. When the specified event occurs, an action or series of actions are performed. Events are often used in conjunction with triggers to automate tasks and enforce business rules.

How can I disable a trigger?

You can disable a trigger using the DISABLE TRIGGER statement. For example, to disable a trigger named ‘trg_UpdateEmployeeDate’ on the ‘Employees’ table, you would use the following SQL command:

DISABLE TRIGGER trg_UpdateEmployeeDate ON Employees;

Can triggers be used to log changes in a database?

Yes, triggers can be very useful for logging changes. You can create a trigger that fires whenever a record is inserted, updated, or deleted in a specific table, and then writes the details of that change to a log table. This can be very useful for auditing purposes.