Triggers and Procedures in MySQL/MariaDB

Triggers and procedures can be powerful tools in MySQL and MariaDB. Here we will take a look at the similarities, differences and how to use them.

The Procedure

A procedure is similar to a function. Procedures (often called Stored Procedures) contain SQL and can have various inputs and outputs.

A simple examples might be the following:

DELIMITER  //

CREATE PROCEDURE GetUsersByEmail(IN emailLike VARCHAR(255))

    SELECT * FROM users WHERE user_email LIKE emailLike;

END //

DELIMITER ;

This procedure takes a simple input emailLike and searches the users table for any records that match the input. The output of this procedure is simply the result of the query.

We can also output into variables.

DELIMITER //

CREATE PROCEDURE GetTotalUsersWithEmail(IN emailLike VARCHAR(255), OUT total INT)

    SELECT COUNT(user_email) INTO total FROM users WHERE user_email LIKE emailLike;

END //

DELIMITER ;

This kind of procedure is less common. Most procedures output a set of results or simply perform a task. To call this procedure:

CALL GetTotalUsersWithEmail('%@gmail.com', @total);
SELECT @total; -- Outputs the total number of users with an email address ending in @gmail.com

The Trigger

Procedures are called manually. Triggers are similar, in that they execute a block of SQL, but are invoked when a certain event is caused.

The three events a trigger can be invoked are INSERT, UPDATE and DELETE. The trigger can be invoked before or after the event.

Here's an example:

DELIMITER //

CREATE TRIGGER UserUpdate AFTER UPDATE ON users FOR EACH ROW
BEGIN
    IF NEW.user_id <> OLD.user_id THEN
        INSERT INTO user_update_log (`user_id`, `field`, `old_value`, `new_value`) VALUES (NEW.user_id, 'user_id', OLD.user_id, NEW.user_id);
    END IF;
END //

DELIMITER ;

The trigger above is a quick audit table example. If the User ID of a user is changed, we want to record the change in a log table. The trigger is invoked AFTER the UPDATE event on a record in the users table. If more than one record is updated, the block between BEGIN and END is executed for each row individually.

If a query that causes a trigger to be invoked if executed within a transaction, the trigger will also be executed within that transaction.

When to use

Procedures are a great way of removing complex queries away from the application and into the database. Triggers are a great way of simplifying the processes involved in manipulating the data.

Triggers and procedures both have the additional advantage of removing code complexity from the application itself. You can modify the procedures and triggers potentially without adversely affecting the application itself.

Written by Kieran on 2019-08-11
This website uses Cookies to enhance your experience. Close Manage