Database Events & Triggers

Every software engineer has a common goal in mind, to create and maintain a high-performance application. In order to achieve such a goal, the infrastructure of the application, the database, needs to be monitored regularly. Database monitoring involves the tracking of the database performance and resources. However, as you may have already guessed, database monitoring is quite time consuming and prone to human-made errors. That’s where database automation comes to the rescue.

Database automation is the use of already established functions to monitor and perform administrative tasks on the database automatically. Sounds great, right? How can this be done though? There are multiple database automation techniques which can be used nowadays. In this blog, we will be discussing two techniques of database automation in depth, Events and Triggers.

What are Triggers?

Triggers, as they may sound, are already coded procedures which are run just before or right after a specific action, INSERT-UPDATE-DELETE, occurs within the database. These procedures can include the modification or verification of incoming data, the calculation which needs to be performed, or the execution of further actions.

When can we use Triggers?

Let’s imagine the following scenario. We have a table called users which carries the basic user details (id, email, password, enabled). Keep in mind that the user has the authority to update any of these fields anytime. Now, we want to keep track of every time the user makes an update in a table named as ‘user_log’ for monitoring and performance purposes. That’s when we can use Triggers to accomplish this desired outcome. Since we would like to keep track of the update action “After” it’s done, this trigger is considered an after-update trigger.

The Triggers Implementation

• Create a trigger and give it a name
• Proceed to set the After-UPDATE condition, so it can be invoked once the action is taken by the user.
• Declare the default value for all to be “unchanged” to make it easier to check only the changed values.
• Proceed to use if statements to check which values were changed by the user. After that, we will use the INSERT command to log the changed values to a new record in the user_log table.

Making an update to the enabled value for the user with the ID 2.

After displaying the ‘user_log’ table, we will be able to find the below record which was automatically created after the update command has been executed with the updated values, while the un-updated ones are displayed as “unchanged”.

What are Events?

Events, also known as temporal triggers, are the actions run at certain time periods previously scheduled. Events are quite crucial for archiving data, and cleaning logs in the database.

When Can We Use Events?

Let’s recall the scenario mentioned in the triggers section above. This time, let’s envision that this system is used by thousands of users, keeping in mind that every time any of those users make an update to their user details, a record will be inserted in the ’user_log’ table since we implemented the triggers on the update action. You can imagine that it will only take some time for the ‘user_log’ table to be full and unable to accept any more entries. In a normal situation, we will need to manually delete the old records to free up some space, which brings us back to the time-consuming and the human-error issues. Thankfully, events can take care of such a scenario and delete the records automatically after a pre-scheduled time has passed, in this case we will set it to 1 month.

Events Implementation

• Create our event and give it a name.
• Set the scheduled time to be after 1 month
• The SQL command to be used after the scheduled time has passed is the DELETE command from the user_log table.

Take note that once the event has been created, it will run instantly, then it will be repeated after the scheduled time has passed, in this case 1 month. Therefore, there is no need to run it once again.

After displaying the user_log table, we can conclude that the event ran successfully and the records were deleted, leaving the table empty for the new records.

The Takeaways

Triggers can be used to automatically run certain a pre-defined set of action before or after the INSERT, UPDATE, DELETE SQL commands for verification of modification purposes, while events can be used to automatically run a pre-defined set of action at a scheduled time for data archive and log cleansing purposes.

You can find other examples on the usage of triggers and events from the below links:

https://dev.mysql.com/doc/refman/5.6/en/create-event.html

https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html