How to Use MySQL Triggers for Recording History
MySQL triggers are most commonly used in ecommerce, which isn’t surprising since MySQL is the most popular open-source management tool for web databases. It also can’t hurt that SQL is still the go to language for accessing and managing content in ecommerce databases. Aside from recording history in databases these triggers serve a variety of other useful functions such as:
- Validating data
- Accessing system functions
- Replicating data/data consistency
- Enforcing business rules
- Generating unique values for new rows in separate files
- Write to other files
- Other logging applications
Being able to record database history is crucial to backing up ecommerce web databases. MySQL triggers can help businesses create another form of backup file. Even though it’s rare, databases can fail. Having a secondary backup or extremely similar table is never a bad idea. A history table is so similar to the main table you can use it to help rebuild a main table, repair harmful changes, or revert back to earlier versions.
What Is Versioning and Why Does It Matter?
Versioning gives ecommerce businesses the option of going back in database history to determine when, where, and who made changes to MySQL relational databases. Using version control in databases also supplies a single, reliable resource to use to recall information as needed. There are several key benefits to using versioning/recording history:
- Keep an in-depth history of database changes
- Deploy from or revert to previously known states
- Use for critical tables that require more than a rolling backup
- Auditing and troubleshooting databases
Versioning along with relational databases like MySQL for ecommerce applications is beneficial when data needs to be updated, as you only need to update one string of data. For example, if an ecommerce business needs to update client emails they could update that row without having to change other information in the table. MySQL triggers make recalling, reverting, and searching relational database history quick and efficient.
An Introduction to MySQL Triggers
MySQL triggers are database objects associated with a table, that become activated when defined actions take place. To run MySQL triggers users require MySQL SUPERUSER privileges. Examples of MySQL trigger statements in a table include UPDATE, INSERT, and DELETE. These trigger statements can be placed before or after an event in database tables.
Using Triggers to Save Database History
Create a history table that looks exactly like your main database table; size and order need to be identical. At the end of the table you will need to add a CREATE TRIGGER statement. The following examples of MySQL trigger coding are only examples of what programming may look like, there are several ways to create triggers. Below is an example of MySQL syntax for creating a basic CREATE TRIGGER statement in your newly created history table.
| CREATE [DEFINER = user] TRIGGER trigger_name trigger_time trigger_event trigger_id ON history_table FOR EACH ROW trigger_body trigger_time { BEFORE | AFTER } |
This sets the ground for using UPDATE, INSERT, AND DELETE triggers. The following is an example of what a basic MySQL insert trigger looks like.
| INSERT INTO history_table SELECT*, ‘update’, CURRENT_USER(), now(), null FROM main_table WHERE id=OLD.id; |
Delete triggers follow the same exact language only ‘update’ is replaced with ‘delete’. Make sure to update history tables as main tables change, or mismatch errors will show throughout table columns. While some programmers will caution against using * with SELECT to return all columns in a table, it’s one of the fastest ways to recall all the information. Indexes must be attributed to any column you name in the WHERE clause of a SELECT query. The WHERE clause must also be updated if other primary or unique key combinations are in use.
- How to Use MySQL Triggers for Recording History - July 8, 2019
Hi Kristen,
Thanks for this post. It seems to be exactly what I was looking for.
My problem is that my main table “member_info” has a unique autoincrement primary key called “record_id” and another unique membership number field called “member_id”. Multiple updates to the same record would attempt to insert duplicate unique values into the history_table, if it were set up exactly like the member_info table. What would you do in that case? Remove the primary key/unique/autoincrement designations from those fields? Should I create a different autoincrement primary key in the history_table?