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.
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
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
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.