Triggers are process that automatically run upon a specific action.

We could specify whether we want the trigger code to run BEFORE or AFTER the specified action.

The actions that could be used in triggers are INSERT, UPDATE or DELETE. This means that whenever there is a either one of these actions on a specific table, we could create code to run BEFORE or AFTER that action.

An interesting aspect of these actions is that, by the nature of the actions, the field values could be different as the action is ran. So we could specify when values to use by notating them with OLD and NEW.

Let’s look at some code and read through it.

CREATE TRIGGER log_product_update

    BEFORE UPDATE ON product_list

    FOR EACH ROW 

 INSERT INTO product_change_history

 SET action = ‘update’,

     product_id = OLD.product_id,

     product_name = OLD.product_name,

     date_time_changed = NOW();

It starts with CREATE TRIGGER and specifying the trigger name, in this case it’s “log_product_update.” This will be a trigger that will be used for automating the process in keeping a history log of any updates that are made to the product table. You could see next comes BEFORE UPDATE, then it specifies the table, in this case “product_list.” So this code will run every time BEFORE an UPDATE action happens to the product_list table. Instead of BEFORE, the command AFTER could be used and in place of the UPDATE keyword, if needed, INSERT or DELETE could be used.

For this action, in this case UPDATE, if it happens to any row, hence, FOR EACH ROW, we will INSERT some values into the table “product_change_history.” You could see that we are adding in a value that reads “update“, also we are logging the previous (OLD) values of the product_id and product_name, with the date. Note the helpful function NOW() that will populate the date with the time of execution as a date stamp.

This trigger will now create a row in the product_change_history table every time an update is made in the product_list table. It will log the previous product_name and product_id.

Hope this has gotten everyone excited to try out creating some new triggers!