Let’s think of a scenario using the logistics database that we’ve used in our previous assignment. The logistics database was based on the ER diagram below.

Your stakeholders has pointed out that the account table is quite important to track any changes that are being made. Sometimes account names change, but we need to keep good track of how they are changed and when they are changed. You immediately think that automating this process of logging changes into a trigger process will be a good idea!

To start you will need to create a separate table to store these changes.

Let’s create a table as described above. We will have a simple id to use as the primary key and keep track of changes, and it set to automatically increment. We will record the account_id that the change occurred, then let’s see if we could track the change itself in a before and after style record. We will also track the date and time that the change was made. Lastly we have an “action” field that we could use to store notes on what action we are recording.

We will now have a table with the properties above. Now let’s read through the MySQL below.

We are creating a trigger named “account_update“, which will be a trigger for updates on any row of the table “account“. You could also see that it will execute AFTER the action, which is UPDATE has happened. Once there is an UPDATE action on the table “account“, it will set a new data entry within the “account_history” table. It will record the action, and also record the OLD and NEW account name as before and after.

As you could notice, this is getting quite exciting.

After you’ve set this up, the account “Central Home Goods” contacted and said that they were changing their business name to “Central Home Furnishings“, but they wanted to keep their existing account. So you quickly use the code below to update their account name to “Central Home Furnishings.”

Soon after you get a another call back and it’s “Central Home Furnishings“, they apologize, but they are going through some management changes and their new owners want to go with the name “Central Home Furniture.” So you know run the code below to make the update.

Now when you check the account table, all looks good and you could see the current name represented correctly. But at some point in time, what if you had to look up some information on an account that had it’s name changed?

You could now check the “account_history” table (this is the exciting part by the way). You could see that the trigger code worked and we now have an informative history of what happened and when.

A quick note for MySQL Workbench is that you could check the different triggers that are set for a specific table using the navigator panel.

In plain MySQL code we could use “SHOW TRIGGERS” and MySQL will provide an output of all the current triggers with it’s details.

There are two videos for this topic, here and then here.