In the article, the use of triggers in MySQL Systems is explained with examples.<\/p>\n
Triggers in MySQL are a special type of MySQL Stored Procedures.<\/p>\n
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.<\/p>\n
Triggers are often used to ensure data integrity and security.<\/p>\n
Triggers in MySQL do not return any value.<\/p>\n
MySQL triggers are run before or after INSERT, UPDATE and DELETE operations.<\/p>\n
Triggers are created with the keyword CREATE TRIGGER.<\/p>\n
CREATE TRIGGER trigger_name BEFORE\/AFTER INSERT\/UPDATE\/DELETE\nON table_name\nFOR EACH ROW\nBEGIN\n -- MySQL and SQL commands\nEND;<\/pre>\nLet’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.<\/p>\n
DELIMITER \/\/\nCREATE TRIGGER new_product_is_added BEFORE INSERT\nON product\nFOR EACH ROW\nBEGIN\n INSERT INTO process(process_name) VALUES(CONCAT(NEW.product_name, ' is added.'));\nEND\/\/\nDELIMITER ;<\/pre>\nAfter creating the trigger, when a new record is added to the “products” table, the record for the product will be added to the “process”.<\/p>\n
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.<\/p>\n
DELIMITER \/\/\nCREATE TRIGGER a_product_is_deleted BEFORE DELETE\nON product\nFOR EACH ROW\nBEGIN\n INSERT INTO product(product_name) VALUES(CONCAT(OLD.product_name, ' is deleted.'));\nEND\/\/\nDELIMITER ;<\/pre>\nNote that the information about the added product is obtained with the keyword NEW, and the information about the deleted product with the keyword OLD.<\/p>\n
We can also use expressions such as variable definition, if, elseif, else, case when, while used in MySQL stored procedures in the trigger.<\/p>\n
However, using too many triggers will negatively affect the performance of the database server.<\/p>\n
Maybe you may have wondered the answer to a question.<\/p>\n
Maybe you may have wondered the answer to a question.<\/p>\n
Does MySQL support DDL Trigger?<\/h3>\n
Unfortunately MySQL does not support DDL Trigger.<\/p>\n
Have a good day.<\/p>\n
<\/div>\n