What is a MySQL Trigger?
A SQL Trigger is a set of SQL statements stored in the database catalog. A SQL Trigger is executed whenever an event that is associated with a table occurs e.g., insert, update or delete. A SQL Trigger is a special type of Stored Procedure. It is special because it is not called directly like a stored procedure. The trigger is a powerful tool for protecting the integrity of the data in MySQL database.
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 in business logic in the database layer.
- Provide an alternative way to run 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 cannot 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.
- A trigger can be defined to 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 is deleted from the table.
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 have the same trigger name defined for different tables though it is a good practice.
- You should name the triggers using the following naming convention: (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 maps 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 DATA, LOAD TABLE, 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.
CREATE TRIGGER trigger_time trigger_name trigger_event ON table_name FOR EACH ROW BEGIN #your query here END;
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, OLD refers to the row before it is updated and NEW refers to the row after it is updated.
Notice that in a trigger defined for INSERT, 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:
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 allows the new trigger to activate after the existing trigger.
The PRECEDED option allows the new trigger to activate before the existing trigger.
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 ;
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?
After creating a trigger, you can display its definition in the data folder, which contains trigger definition file.
A trigger is stored as a plain text file in the database folder named /data_folder/database_name/table_name.trg.
MySQL provides you with an alternative way to display 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’;
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.
SHOW TRIGGERS [FROM | IN] database_name [LIKE expr | WHERE expr];
To view all triggers in current database, query as follows:
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 name of the trigger e.g., before_employee_update.
Event – specifies the 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 SQL mode 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. Yu cannot modify an existing trigger.