Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In the article, the use of triggers in MySQL Systems is explained with examples.
What is Trigger?
Triggers in MySQL are a special type of MySQL Stored Procedures.
The most important feature that distinguishes triggers from stored procedures is that we need to run the stored procedures manually, while triggers work spontaneously when changes are made to the data.
Triggers are often used to ensure data integrity and security.
Triggers in MySQL do not return any value.
Create a MySQL Trigger
MySQL triggers are run before or after INSERT, UPDATE and DELETE operations.
Triggers are created with the keyword CREATE TRIGGER.
1 2 3 4 5 6 | CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW BEGIN -- MySQL and SQL commands END; |
Let’s create a trigger that will record information about the added product to the “process” table when a product is added to the “products” table.
1 2 3 4 5 6 7 8 | DELIMITER // CREATE TRIGGER new_product_is_added BEFORE INSERT ON product FOR EACH ROW BEGIN INSERT INTO process(process_name) VALUES(CONCAT(NEW.product_name, ' is added.')); END// DELIMITER ; |
After creating the trigger, when a new record is added to the “products” table, the record for the product will be added to the “process”.
Using the trigger creation structure above, let’s create the trigger that inserts the deleted product name to the “process” table if a record is deleted from the “products” table.
1 2 3 4 5 6 7 8 | DELIMITER // CREATE TRIGGER a_product_is_deleted BEFORE DELETE ON product FOR EACH ROW BEGIN INSERT INTO product(product_name) VALUES(CONCAT(OLD.product_name, ' is deleted.')); END// DELIMITER ; |
Note that the information about the added product is obtained with the keyword NEW, and the information about the deleted product with the keyword OLD.
We can also use expressions such as variable definition, if, elseif, else, case when, while used in MySQL stored procedures in the trigger.
However, using too many triggers will negatively affect the performance of the database server.
Maybe you may have wondered the answer to a question.
Maybe you may have wondered the answer to a question.
Does MySQL support DDL Trigger?
Unfortunately MySQL does not support DDL Trigger.
Have a good day.
