Triggers
Triggers are special types of stored procedures that are automatically executed in response to specific events, such as a table update or insertion. They can be used for tasks such as enforcing data integrity or maintaining a history of changes
If you’re a beginner in this field, you may have heard about the mysterious concept of “triggers”. No, we’re not talking about the type you pull to shoot someone, but rather triggers in the database world. Triggers are like having a little helper who keeps watch over your database, and can perform tasks for you automatically. We’re going to dive into the basics of MySQL triggers, and show you how to use them to make your database management life easier and more efficient.
Some example use cases:
- Audit Trail: Keep track of changes made to a database by automatically recording the time, user, and action taken in a separate table using triggers.
- Data Validation: Use triggers to enforce business rules, check for data integrity and reject any invalid data before it is stored in the database.
- Auto-Updating Fields: Automatically update fields such as “last updated” or “total sales” whenever a change is made to the database, without having to manually update it.
- Sending Notifications: Automatically send email notifications to specific users or groups when a particular action takes place, such as a new order being placed.
- Data Synchronization: Automatically synchronize data between multiple databases or tables, ensuring that all changes made in one place are reflected in another.
- Enforcing Constraints: Use triggers to enforce constraints, such as ensuring that only one active record is allowed for a particular user.
- Logging: Log all database transactions, such as inserts, updates, and deletes, for auditing and troubleshooting purposes.
- Preventing Data Loss: Automatically prevent data loss by using triggers to backup data in real-time.
- Dynamic Calculations: Automatically calculate fields based on other fields, such as calculating the total cost of an order based on the individual item prices.
Audit Trail
- Create a table to store the audit trail information, such as the date and time of the change, the user who made the change, and the type of change (insert, update, or delete).
- Create the trigger that will automatically insert a new record into the audit trail whenever a change is made to the data in the main table. In this example, we’ll create a trigger for an “orders” table.
CREATE TABLE audit_trail (
id INT AUTO_INCREMENT PRIMARY KEY,
date_time DATETIME NOT NULL,
user VARCHAR(255) NOT NULL,
action ENUM('insert', 'update', 'delete') NOT NULL,
record_id INT NOT NULL
);
CREATE TRIGGER audittrail_orders
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
BEGIN
DECLARE @user VARCHAR(255);
DECLARE @action VARCHAR(20);
DECLARE @record_id INT;
SET @user = USER();
IF (INSERTING()) THEN
SET @action = 'insert';
SET @record_id = NEW.id;
ELSEIF (UPDATING()) THEN
SET @action = 'update';
SET @record_id = NEW.id;
ELSE
SET @action = 'delete';
SET @record_id = OLD.id;
END IF;
INSERT INTO audit_trail (date_time, user, action, record_id)
VALUES (NOW(), @user, @action, @record_id);
END;
Data Validation
- Create the table you want to validate data for. In this example, let’s use a “customers” table.
- Create the trigger that will enforce the business rule that the customer’s age must be over 18.
- Now, whenever you attempt to insert a new customer record into the “customers” table, the trigger will be executed first, and will automatically reject any records where the customer’s age is less than 18. If a record is rejected, an error message will be displayed, letting you know why the record was not inserted.
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
age INT NOT NULL
);
CREATE TRIGGER validate_age
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
IF (NEW.age < 18) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Customers must be over 18 years old';
END IF;
END;
mysql> INSERT INTO customers (name, email, age) VALUES ('John Doe', 'johndoe@example.com', 17);
ERROR 1644 (45000): Customers must be over 18 years old
Auto-Updating Fields
- Create the table you want to auto-update fields for. In this example, let’s use an “orders” table.
- Create the trigger that will automatically update the “date_updated” field whenever a change is made to the “orders” table.
- Now, whenever you insert a new order or update an existing order, the “date_updated” field will automatically be updated to the current date and time.
CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, date_placed DATETIME NOT NULL, total_amount DECIMAL(10, 2) NOT NULL, date_updated DATETIME NOT NULL ); CREATE TRIGGER auto_update_date AFTER INSERT OR UPDATE ON orders FOR EACH ROW BEGIN SET NEW.date_updated = NOW(); END; mysql> INSERT INTO orders (date_placed, total_amount) VALUES (NOW(), 100.00); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM orders; +----+---------------------+-------------+---------------------+ | id | date_placed | total_amount | date_updated | +----+---------------------+-------------+---------------------+ | 1 | 2021-05-17 13:00:00 | 100.00 | 2021-05-17 13:00:00 | +----+---------------------+-------------+---------------------+
Notifications
- Create the table you want to send notifications for. In this example, let’s use a “orders” table.
- Create the “mail_queues” table to store the email notifications.
- Create the trigger that will insert a record into the “mail_queues” table whenever the status of an order is changed to “Completed”.
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
date_placed DATETIME NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
status ENUM('Pending', 'Processing', 'Completed') NOT NULL
);
CREATE TABLE mail_queues (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
to_email VARCHAR(255) NOT NULL,
subject VARCHAR(255) NOT NULL,
message TEXT NOT NULL,
status ENUM('Pending', 'Sent') NOT NULL DEFAULT 'Pending',
date_created DATETIME NOT NULL
);
DELIMITER $$
CREATE TRIGGER send_notification
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
IF (OLD.status != 'Completed' AND NEW.status = 'Completed') THEN
-- insert a record into the mail_queues table
INSERT INTO mail_queues (order_id, to_email, subject, message, date_created)
VALUES (NEW.id, 'customer@example.com', 'Order Completed',
'Your order #' || NEW.id || ' has been completed. Total amount: ' || NEW.total_amount || '.', NOW());
END IF;
END $$
DELIMITER ;
Data Synchronization
- Create the “customers” table in both databases.
- Create the trigger in “DB1” to insert the same customer into “DB2” when a new customer is inserted into “DB1”.
-- database DB1 CREATE TABLE customers ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, created_at DATETIME NOT NULL ); -- database DB2 CREATE TABLE customers ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, created_at DATETIME NOT NULL ); -- the trigger DELIMITER $$ CREATE TRIGGER sync_customer AFTER INSERT ON customers FOR EACH ROW BEGIN -- insert the same customer into the "customers" table in "DB2" INSERT INTO DB2.customers (first_name, last_name, email, created_at) VALUES (NEW.first_name, NEW.last_name, NEW.email, NEW.created_at); END $$ DELIMITER ;
Enforcing Constraints
- Create the “orders” table.
- Create the trigger to enforce the constraint on the “total” field.
DELIMITER $$
CREATE TRIGGER enforce_total
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
-- check if the total value is within the specified range
IF NEW.total < 10 OR NEW.total > 100 THEN
-- if the total is outside of the range, raise an error
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Total must be between $10 and $100.';
END IF;
END $$
DELIMITER ;
Logging
- Create the “employees” table.
- Create a “logs” table to store the changes made to the “employees” table.
- Create the trigger to log the changes made to the “employees” table.
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
updated_at DATETIME NOT NULL
);
CREATE TABLE logs (
id INT AUTO_INCREMENT PRIMARY KEY,
employee_id INT NOT NULL,
field VARCHAR(255) NOT NULL,
old_value VARCHAR(255),
new_value VARCHAR(255),
created_at DATETIME NOT NULL
);
-- The trigger
DELIMITER $$
CREATE TRIGGER log_employee_changes
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
-- check if any of the fields were changed
IF OLD.first_name != NEW.first_name THEN
-- insert a log entry for the first name field
INSERT INTO logs (employee_id, field, old_value, new_value, created_at)
VALUES (OLD.id, 'first_name', OLD.first_name, NEW.first_name, NOW());
END IF;
IF OLD.last_name != NEW.last_name THEN
-- insert a log entry for the last name field
INSERT INTO logs (employee_id, field, old_value, new_value, created_at)
VALUES (OLD.id, 'last_name', OLD.last_name, NEW.last_name, NOW());
END IF;
IF OLD.email != NEW.email THEN
-- insert a log entry for the email field
INSERT INTO logs (employee_id, field, old_value, new_value, created_at)
VALUES (OLD.id, 'email', OLD.email, NEW.email, NOW());
END IF;
END $$
DELIMITER ;
Preventing Data Loss
An example of preventing data loss using a trigger could be when you want to ensure that data is never deleted from a critical table. For example, let’s say you have a “customers” table and you want to prevent any data from being deleted from the table. Here’s how you could set up the trigger.
- Create the “customers” table.
- Create the trigger to prevent data from being deleted from the “customers” table.
CREATE TABLE customers ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, deleted_at DATETIME ); -- the trigger DELIMITER $$ CREATE TRIGGER prevent_customer_deletion BEFORE DELETE ON customers FOR EACH ROW BEGIN -- instead of deleting the row, update the deleted_at field SET NEW.deleted_at = NOW(); UPDATE customers SET deleted_at = NOW() WHERE id = OLD.id; -- stop the DELETE statement SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Deleting customers is not allowed'; END $$ DELIMITER ;
Now, whenever you try to delete a customer from the “customers” table, the trigger will be executed and the DELETE statement will be stopped. Instead of deleting the customer, the trigger will update the “deleted_at” field, marking the customer as deleted. This way, you can ensure that critical data is never lost, even if it has been marked as deleted.
Dynamic Calculations
- Create the “orders” and “order_items” tables.
- Create the trigger to dynamically calculate the total cost of an order.
CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT NOT NULL, total_cost DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL ); -- the trigger DELIMITER $$ CREATE TRIGGER update_order_total_cost AFTER INSERT ON order_items FOR EACH ROW BEGIN -- calculate the total cost of the order DECLARE total_cost DECIMAL(10,2); SET total_cost = (SELECT SUM(cost) FROM order_items WHERE order_id = NEW.order_id); -- update the total cost of the order in the orders table UPDATE orders SET total_cost = total_cost WHERE id = NEW.order_id; END $$ DELIMITER ;
Now, whenever a new item is inserted into the “order_items” table, the trigger will be executed and the total cost of the order will be calculated and updated in the “orders” table. This way, you can ensure that the total cost of each order is always up-to-date and accurate.
