What is a MySQL Trigger?
A SQL Trigger is a set of SQL statements that are stored in the database catalogue and allow us to protect the integrity of the data in MySQL database.
A trigger is actually a named database object that is associated with a table, and that activates when a specific event occurs for the table.
A trigger can be defined to activate when any SQL statement inserts, updates, or deletes rows in the associated table.
A SQL Trigger is a special type of Stored Procedure. It is special because it is not called directly as we call a stored procedure.
The main difference between a trigger and a stored procedure is that a trigger called automatically when a data modification event is maid against a table whereas a stored procedure must be called explicitly.
SQL Triggers Advantages:
- Powerful tool for protecting the integrity of the data in MySQL database.
- Can catch errors that encountered in business logic in the database layer.
Provide an alternative way to run MySQL Scheduled Tasks. - Very useful to automate some database operations such as logging, auditing, etc.
SQL Triggers Disadvantages:
- Can only provide an extended validation and they are not capable to replace all the validations.
- They are invoked and executed invisible from the client applications, therefore, it is difficult to figure out what happened in the database layer.
- May increase the overhead of the database server.
Useful Notes:
- AYou can define a trigger that can be invoked either before or after the data is changed by INSERT, UPDATE or DELETE statement.
- TRUNCATE statement removes all data of a table but does not invoke the trigger associated with that table.
- Some statements that use the INSERT statement behind the scene such as REPLACE or LOAD DATA statement cause to invoke corresponding triggers associated with the table.
MySQL Triggers can be defined for a table in the following ways:
Before MySQL version 5.7.2, you could define maximum six triggers for each table:
- BEFORE INSERT – invoked before data is inserted into the table.
- AFTER INSERT – invoked after data is inserted into the table.
- BEFORE UPDATE – invoked before data is updated into the table.
- AFTER UPDATE – invoked after data is updated into the table.
- BEFORE DELETE – invoked before data is deleted from the table.
- AFTER DELETE – invoked after data from the table is deleted.
From MySQL version 5.7.2+, you can define multiple triggers for the same trigger event and action time.
MySQL Trigger naming conventions:
- You must use a unique name for each trigger associated with a table.
- However, you can also have the same trigger name defined for the different tables though it is a good practice.
- You should follow the following naming convention when naming the triggers: (BEFORE | AFTER)_tableName_(INSERT | UPDATE | DELETE)
For example, before_order_update.
The following naming convention is as good as above:
tableName_(BEFORE | AFTER)_(INSERT | UPDATE | DELETE)
For example, order_before_insert.
How the Triggers are stored in MySQL?
MySQL stores triggers in a data directory e.g., /data/classicmodels/ with the files named tableName.TRG and triggername.TRN.
The tableName.TRG file is used to map the trigger to the corresponding table.
The triggername.TRN file contains the trigger definition.
MySQL triggers can be backed up by copying the trigger files to the backup folder.
You can also back up the triggers using mysqldump tool.
What are the limitations of Triggers in MySQL?
MySQL triggers cover all features defined in the standard SQL, but it have some limitations.
MySQL triggers cannot:
- Use SHOW, LOAD TABLE, LOAD DATA, BACKUP DATABASE, RESTORE, FLUSH and RETURN statements.
- Use statements that commit or rollback implicitly or explicitly such as COMMIT, ROLLBACK, START TRANSACTION, LOCK/UNLOCK TABLES, ALTER, CREATE, DROP, RENAME, etc.
- Use Prepared statements such as PREPARE, EXECUTE, etc.
- Use Dynamic SQL statements.
How the create Triggers in MySQL?
You can create a trigger in MySQL by using CREATE TRIGGER statement.
Syntax:
CREATE TRIGGER trigger_time trigger_name trigger_event ON table_name
FOR EACH ROW
BEGIN
#your query here
END;
Example:
DELIMITER //
CREATE TRIGGER before_employee_update ON employee
FOR EACH ROW
BEGIN
INSERT INTO employee_audit SET action = 'update', employee_number = OLD.employee_number, last_name = OLD.last_name, changed_at = NOW();
END//
DELIMITER ;
Inside the body of the trigger, we used the OLD keyword to access employee_number and last_name column of the row affected by the trigger.
In the UPDATE trigger, we used OLD keyword that refers to the row before it is updated and NEW keyword that refers to the row after it is updated.
Notice that when you define a INSERT trigger, you can use NEW keyword only.
However, in the trigger defined for DELETE, there is no new row so you can use the OLD keyword only.
How to view all the Triggers in MySQL?
To view all triggers in the current database, you can use SHOW TRIGGERS statement as follows:
SHOW TRIGGERS;
How to create Multiple Triggers for the same Trigger event and Action name in MySQL?
The syntax for creating the first trigger remains same.
In case you have multiple triggers for the same event in a table, MySQL will invoke the triggers in the order that they were created.
To change the order of triggers, you need to specify FOLLOWS or PRECEDES after the FOR EACH ROW clause.
The FOLLOWS option permits the new trigger to activate after the existing trigger.
The PRECEDED option allows the new trigger to activate before the existing trigger.
Syntax:
DELIMITER //
CREATE TRIGGER trigger_name
[BEFORE | AFTER] [INSERT | UPDATE | DELETE] ON table_name
FOR EACH ROW [FOLLOWS | PRECEDES] existing_trigger_name
BEGIN
#your query here
END//
DELIMITER ;
Example:
DELIMITER //
CREATE TRIGGER before_product_update
BEFORE UPDATE ON product
FOR EACH ROW
BEGIN
INSERT INTO price_log(product_code, price) VALUES(OLD.product_code, OLD.price);
END//
CREATE TRIGGER before_product_update_2
BEFORE UPDATE ON product
FOR EACH ROW FOLLOWS before_product_update
BEGIN
INSERT INTO user_change_log(product_code, updated_by) VALUES(OLD.product_code, user());
END//
DELIMITER ;
How to manage Triggers in MySQL?
Once you have created a trigger, you can easily display it's definition in the data folder, which contains definition file of the trigger.
A trigger is stored in a plain text file in the database folder named /data_folder/database_name/table_name.trg.
MySQL provides us with an alternative way to show the trigger by querying the triggers table in the information_schema database as follows:
SELECT * FROM information_schema.triggers WHERE trigger_schema = ‘database_name’ AND trigger_name = ‘trigger_name’;
This statement allows you to view both content of the trigger and its metadata such as associated table name and definer, which is the name of MySQL User who created the trigger.
If you want to retrieve all triggers in a particular database, use need to query as follows:
SELECT * FROM information_schema.triggers WHERE trigger_schema = ‘database_name’;
If you want to retrieve all triggers associated with a particular table, use need to query as follows:
SELECT * FROM information_schema.triggers WHERE trigger_schema = ‘database_name’ AND event_object_table = ‘table_name’;
Example:
SELECT * FROM information_schema.triggers WHERE trigger_schema = ‘HRMS_DB’ AND event_object_table = ‘employee’;
Explain SHOW TRIGGERS statement in MySQL?
SHOW TRIGGERS statement allows you to display triggers in a particular database.
Syntax:
SHOW TRIGGERS [FROM | IN] database_name [LIKE expr | WHERE expr];
Example:
To view all triggers in current database, query as follows:
SHOW TRIGGERS;
To view all triggers in current database associated with a specific table, query as follows:
SHOW TRIGGERS WHERE `table` = ‘employee’;
To view all triggers in a specific database, query as follows:
SHOW TRIGGERS FROM hrms_database;
To view all triggers in a specific database associated with a specific table, query as follows:
SHOW TRIGGERS FROM hrms_database WHERE `table` = ‘employee’;
Notice that we use backquote (`) to wrap the table column because table is the reserved keyword in MySQL.
MySQL returns the following columns when you execute the SHOW TRIGGERS statement:
Trigger – stores the trigger name e.g., before_employee_update.
Event – specifies the type of event e.g., INSERT, UPDATE or DELETE.
Table – specifies the associated table e.g., employee.
Statement – stores the statement or compound statement to be executed upon trigger invocation.
Timing – accepts two values, BEFORE or AFTER, and specifies the invocation time.
Created – logs the created time.
sql_mode – specifies the mode of the SQL when the trigger executes.
Definer – defines the account who created the trigger.
Note that to execute the SHOW TRIGGERS statement, you must have the SUPER privilege.
How to remove a trigger in MySQL?
To remove an existing trigger, you can use DROP TRIGGER statement as follows:
DROP TRIGGER table_name.trigger_name;
DROP TRIGGER employee.before_employee_update;
To modify a trigger, you have to delete it first and then recreate it with new code. You cannot modify an existing trigger.