Triggers in MySQL

Yusuf SEZER

5 years ago

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.

CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE
ON table_name
FOR EACH ROW
BEGIN
  -- MySQL and SQL commands
END;

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.

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 ;

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.

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 ;

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.

Does MySQL support DDL Trigger?

Unfortunately MySQL does not support DDL Trigger.

Have a good day.