Tracking Changes to a Table Using Triggers

Allison Tharp

2016-01-08

How To

MySQL

Perhaps the most important requirement in the gunsmith database that I am designing is tracking every change in the database. I had a couple of ideas for tracking these changes. One idea was to include the columns ‘isupdated’ and ‘previous_record’ to each table that I wanted to track changes to. If something needed to be updated, a new entry would be entered and the previous primary key would be inserted into the ‘previous_record’ column and ‘isupdated’ would turn from 0 to 1. However, it would be very difficult to pull up all of the changes for a particular entry (for instance, all of the changes to a specific customer). In order to make searching easier, I thought I could add a ‘secondary key’ column to each table that I wanted to track. This ‘secondary key’ would be consistent for each customer in the customer table or each acquisition transaction in the acquisition table. I would still have the ‘isupdated’ and ‘previous_record’ columns and track changes as in the other method, but this way I could search for all of the changes to a specific customer by simply searching for that customer’s ‘secondary key’. I figured there is probably a standard way to track changes to a database and found a much simpler method that creates a history table that uses triggers. This method is more commonly referred to as using prepared statements. A trigger is an object that activates when a table is modified by an INSERT, UPDATE, and/or DELETE statement. In my case, I have a table for each customer that is involved in a transaction at the gun store. If my friend inputs something incorrectly into the database when they add a new customer, I want them to be able to correct it. However, due to federal regulations, I also need to be able to go back to any date and see what was in the database for a specific customer at a specific time. We will create a customer_history table that will activate anytime something is inserted, updated, or deleted from the customer table. We will track what the new values are and when they were updated. We will create this table at the same time that we create the customer table; therefore, we will have a complete history of all customers in the history table. If we were to create the customer_history table after creating and using the customer table, we will not have the historical records for anything done in the customer table prior. Triggers are great because they will automate this process – all we will have to worry about is the customer table.

Create customer Table

I created the customer table using the command below: [sql] CREATE TABLE customer ( customer_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, firstname VARCHAR(50), lastname VARCHAR(50), company VARCHAR(50), address1 VARCHAR(75) NOT NULL, address2 VARCHAR(75), city VARCHAR(50) NOT NULL, state VARCHAR(2) NOT NULL, zip MEDIUMINT(5) UNSIGNED NOT NULL, phone INT(10), licensenum VARCHAR(20) NOT NULL); [/sql] Before we enter any data into this table, we need to create the table which will hold all of the historical data. We will create a new table with the name customer_history. This new table will start out being just a copy of customer: [sql] CREATE TABLE customer_history LIKE customer; [/sql] Next, we will add the columns ‘action’, ‘revision’, and ‘dt_datetime’. The ‘action’ column will show what action was done to the table (UPDATE, INSERT, or DELETE), ‘revision’ will increment each time a customer is edited (beginning with 1 when it is first created), and ‘dt_datetime’ will give the date and time that the action occurred. We also want to change the primary key from being simply the customer id to being a combination of customer id and revision. This is because each customer id may be in the table more than once but a customer_id and revision number will be unique (if the customer is edited, it will go in as a new revision). [sql] ALTER TABLE customer_history MODIFY COLUMN customer_id INT NOT NULL, DROP PRIMARY KEY, ENGINE = MyISAM, ADD action VARCHAR(8) DEFAULT ‘insert’ FIRST, ADD revision INT(6) NOT NULL AUTO_INCREMENT AFTER action, ADD dt_datetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER revision, ADD PRIMARY KEY (customer_id, revision); [/sql]

Create The Trigger

Now that the table is ready, we can set the triggers. Creating triggers is much like creating a new database. The general formula is: [sql] CREATE TRIGGER trigger_name AFTER INSERT ON table_to_trigger_from FOR EACH ROW INSERT INTO history_table [trigger event]; [/sql] We will need to create three triggers, one each for INSERT, UPDATE, and DELETE. [sql] CREATE TRIGGER customer_ai AFTER INSERT ON customer FOR EACH ROW INSERT INTO customer_history SELECT ‘insert’, NULL, NOW(), d.* FROM customer AS d WHERE d.customer_id = NEW.customer_id; [/sql] This object triggers automatically when a row is inserted into the customer table. It automatically puts ‘insert’ into the ‘action’ column, auto increments the revision, and puts the current date and time into the dt_datetime column. The d.* portion of the query selects the rest of the row that was inserted and puts it into this row in the customer_history table as well. The update trigger is very much the same: [sql] CREATE TRIGGER customer_au AFTER UPDATE ON customer FOR EACH ROW INSERT INTO customer_history SELECT ‘update’, NULL, NOW(), d.* FROM customer AS d WHERE d.customer_id = NEW.customer_id; [/sql] The DELETE trigger is also very similar. However, this triggers before a delete action occurs on the customer table: [sql] CREATE TRIGGER customer_bd BEFORE DELETE ON customer FOR EACH ROW INSERT INTO customer_history SELECT ‘delete’, NULL, NOW(), d.* FROM customer AS d WHERE d.customer_id = OLD.customer_id; [/sql]

Check Both Tables