MySQL Triggers is a set of SQL statements that are executed automatically on some action in database tables. Triggers are stored in database catalogue itself. It is generally used to execute scheduled task with some event. Triggers are also useful for checking integrity at the database level. Triggers are called or executed by the MySQL server itself, it is not run manually by a program or some coding.
Advantages of Trigger:
- It allows us to schedule an event on some action.
- It allows some level of data integration.
- It helps in auditing the changed values in the tables or maintains the logs / history of data.
- Increase the performance as database not needs to compile it every time.
- It contains some business logic also and hence reduces network usage and improves response time.
Disadvantages of Trigger:
- It increases the overhead at the database level.
- It is difficult to debug and manage business logics as it is not transparent.
MySQL provides 6 types of actions or events in the form of Triggers:
- After delete: Acts after the deletion of a record form the table
- Before delete: Acts before the deletion of a record form the table.
- After Update: Acts after the update of a record in a table.
- Before Update: Acts before the update of a record in a table.
- After Insert: Acts after the insertion of a record in a table.
- Before Insert: Acts before the insertion of a record in a table.
Note: Replace and load data statements are treated as insert statements. So after insert and before insert triggers also respond on Replace and Load data statements.
Basic syntax of a trigger definition:
Create Trigger (BEFORE | AFTER) (INSERT | DELETE | UPDATE) on Table Name.
Triggers at physical level
Triggers in MySQL create two files at the physical level, these are:
TableName.TRG and triggerName.TRN
Trg file maps the trigger to table for which it is created and TRN file contains the trigger definition.
Example of a trigger:
Let’s say we have an Employee table as:
CREATE TABLE employees ( id bigint(11) NOT NULL AUTO_INCREMENT, name varchar(250) NOT NULL, dob datetime DEFAULT NULL, change_date datetime DEFAULT NULL, PRIMARY KEY (id) )
And log table to audit the changes as:
CREATE TABLE employees_log ( idint(11) NOT NULL AUTO_INCREMENT, name varchar(250) NOT NULL, change_date datetime DEFAULT NULL, action varchar(50) DEFAULT NULL, PRIMARY KEY (id) )
Now we want to create a trigger which will update the change date in the table before updating a single row of the table. We need to use BEFORE UPDATE trigger as:
DELIMITER $$ CREATE TRIGGER monitor_change_date BEFORE UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO employees_log SET action = 'update', employeeNumber = OLD.id, name = OLD.name, change_date = NOW(); END$$ DELIMITER;
This trigger insert a row in log table before updating a single row of the employee table so audit the changes done in the employee table. IN the trigger definition we specify that trigger need to run before updating the employee table. It runs each time for a single row update.
Similarly we can create all other actions like deletion, insertion and also change the priority of before / after.
Note: Delimiter concept is same as described in stored procedure chapter; it is used to specify the separation between two commands.
We can check the triggers information in the Triggers table under the information_schema database. We can find all the trigger details with the following command:
SELECT * FROM Information_Schema.Triggers WHERE Trigger_schema = ‘databaseName’ AND Trigger_name = 'triggerName';
We can also show the triggers by using its name in the SHOW command:
SHOW triggers like ‘%triggerName%’;
Drop the trigger
MySQL provides a easy way to dropping the trigger by a single query as:
Drop trigger tableName.triggerName;
For our above example where table name is Employees and the trigger name is monitor_change_date then the drop trigger query will be written is:
Drop trigger Employee.monitor_change_date;