MySql Triggers And History Table

The previous lesson of MySql Lessons series was about MySql NOT IN() Function and in this lesson we will learn about triggers in MySql.



First of all let me define what a Trigger really is? A trigger is a database object associated with a table and is activated when an event (Before Insert, After Insert, Before Update, After Update, Before Delete or After Delete) occurs on the table.

Let us say we have a MySql table `product_sale` and when a new record is inserted, updated or deleted in `product_sale` table, we want to make an entry in another table `product_sale_history` which keeps the history of `product_sale` table to keep a track of what happened to the data of `product_sale` table.

The structure of `product_sale` table is given below:



Structure of `product_sale_history` will be slightly different from `product_sale`. The image given below shows the structure of `product_sale_history` table:



You can see in the above image the history table has columns which were not there in the original `product_sale` table i.e old_price, new_price and action. We need these columns in the history table to keep track of what actually happened to the data of `product_sale` table. The `action` column will let us know what function was performed on the data of `product_sale` table and `old_price` and `new_price` will let us know what the price of product was before and after performing the action.

Let us write a MySql trigger that will be activated when data is inserted in `product_sale` table and it will insert the same data in `product_sale_history` table.

DELIMITER $$
CREATE TRIGGER insert_product_data AFTER INSERT ON product_sale 
FOR EACH ROW BEGIN 
INSERT INTO `product_sale_history` VALUES('', NEW.code, 0, NEW.price, 'INSERT');
END;
$$
DELIMITER; 

In the above trigger the statement CREATE TRIGGER creates a trigger named `insert_product_data`. It is associated with `product_sale` table and is activated after data insertion in `product_sale` table.

For each row insertion in `product_sale` table the below query will insert a row in `product_sale_history` table.

INSERT INTO `product_sale_history` VALUES('', NEW.code, 0, NEW.price, 'INSERT');

The keyword NEW in the above query refers to the new row inserted in the `product_sale` table. Which means that NEW.code will contain the value of last inserted code in `product_sale` table.

Similarly we will write a trigger activated AFTER UPDATE ON `product_sale` and will insert a row in `product_sale_history` table.

DELIMITER $$
CREATE TRIGGER update_product_data AFTER UPDATE ON product_sale 
FOR EACH ROW BEGIN 
INSERT INTO `product_sale_history` 
VALUES('', NEW.code, OLD.price, NEW.price, 'UPDATE');
END;
$$
DELIMITER; 

Notice in the above trigger we have used a keyword OLD.price. This keyword OLD refers to the old data of a row before the row was updated. In an INSERT TRIGGER we can use only NEW keyword, in UPDATE TRIGGER we can use both NEW and OLD keywords and in DELETE TRIGGER we can use only OLD keyword.

Now let us write a DELETE TRIGGER and just like the INSERT and UPDATE TRIGGERS we will insert data in `product_sale_history` when a row is deleted from `product_sale`.

DELIMITER $$
CREATE TRIGGER delete_product_data AFTER DELETE ON product_sale 
FOR EACH ROW BEGIN 
INSERT INTO `product_sale_history` VALUES('', OLD.code, OLD.price, 0, 'DELETE');
END;
$$
DELIMITER; 

Once we have written the above triggers for our `product_sale` table we can tell the complete history of a product. Let us say we insert a product in `product_sale` table and set the price of product equal to 30. Then we update it's price to 50 and then again we update it's price equal to 40. And eventually we delete it. What do we have in our `product_sale` table? No data at all, we can not tell what happened to the data of `product_sale` table. But luckily we have out `product_sale_history` table and all the history is maintained in it. Our `product_sale` table will be empty but our `product_sale_history` table will look like this:



We can not associate more than one (AFTER INSERT or BEFORE INSERT or AFTER UPDATE or BEFORE UPDATE or AFTER DELETE or BEFORE DELETE) TRIGGERS with a single table. There can only be one of each mentioned triggers associated with a table.

How to delete a trigger?

Deleting a MySql trigger is very simple. Just execute the drop query to delete a trigger like this:

DROP TRIGGER IF EXISTS `trigger name`

If we want to delete our `delete_product_data` trigger our query will look like this.

DROP TRIGGER IF EXISTS `delete_product_data`

Also if we drop a table the triggers associated with it are also dropped.

How to check if a trigger is created?

To check all the triggers associated with `product_sale` table, we can use the query given below:

SHOW TRIGGERS LIKE 'product_sale'

The above query will list all the triggers associated with `product_sale` table.

Stay tuned for more lessons...