Triggering(SQL) intensifies
In today’s exciting post we will be discussing MySQL triggers, a powerful mechanism introduced with the release of MySQL 5.0 back in 2005!
In part one we will be discussing the basics of triggers, what they are and what they are commonly used for, followed by part two, where we will focus on implementing a simple go application using GORM to enforce triggers onto a MySQL table running on the InnoDB engine.
What are triggers?
Triggers are defined objects in databases, consisting of a set of SQL statements, and are used to respond to changes in data. Unlike stored procedures, triggers can not be invoked at will, and are performed when modifications are made to the database. Modifications include AFTER or BEFORE insertions, updates , deletions on tables.
The six main triggers types we can define for a table are:
- AFTER INSERT : active trigger logic after a new row is inserted into the table.
- AFTER UPDATE: active trigger logic after a row is modified in the table.
- AFTER DELETE: active trigger logic after a row is removed from the table.
- BEFORE INSERT: active trigger logic before a new row is inserted into the table.
- BEFORE UPDATE: active trigger logic before a row is modified in the table.
- BEFORE DELETE: active trigger logic before a row is deleted from the table.
Triggers can be applied on row or statement level. In this article we will mostly be focusing on row-based level triggers. But in short, the main difference is that for row-level triggers, post modification logic is applied on every row in the transaction, whilst for statement level triggers, post modification logic is only applied once each transaction. Often, triggers are used for integrity checks, change logs, referential constraints and to apply post operation logic which manipulates the data that is being modified. Thus it is a powerful tool when it comes to maintaining the integrity of the database, and reducing the risk of introducing human error.
Example
As mentioned before, one of the use-cases of triggers is to apply post operation logic to the data that is being modified. One example could be to automatically update the price of a product based on where the product is being sold in Europe. Given the country, the VAT tax differs, hence, the total price of a product.
A simple Products table structure could be described as :
CREATE TABLE Products (
ProductID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255),
Country VARCHAR(100),
Price DECIMAL(10, 2),
ReleaseDate DATE
);The use-case is simple, we want to add products to the products table, and ensure that the price is re-calculated according to the country column’s value. Thus, it would make sense to use the BEFORE INSERT trigger, as we would want to calculate the new price before we insert it into the table.
DELIMITER $$
CREATE TRIGGER adjust_price_on_insert
BEFORE INSERT ON Products
FOR EACH ROW
BEGIN
-- Netherlands: Add 21% VAT
IF NEW.Country = 'Netherlands' THEN
SET NEW.Price = NEW.Price * 1.21;
-- Germany: Add 19% VAT
ELSEIF NEW.Country = 'Germany' THEN
SET NEW.Price = NEW.Price * 1.19;
-- France: Add 20% VAT
ELSEIF NEW.Country = 'France' THEN
SET NEW.Price = NEW.Price * 1.20;
-- Default VAT (e.g. 10%) for other countries
ELSE
SET NEW.Price = NEW.Price * 1.10;
END IF;
END $$
DELIMITER ;In hindsight, the code looks quite self-explanatory. Before each row is inserted, given the country value, the price is re-calculated accordingly. You may be wondering what the delimiter is used for before defining the triggers. By using the delimiter, MySQL knows where the trigger logic ends, which is when the delimiter is reached.
Next to that, NEW and OLD are special keywords that are used to reference the values of records that are being inserted or updated. NEW indicates the record that is being inserted, whilst OLD indicates the record that is being update. Using NEW and OLD it is easy to compare between the previous and new states of a record.
Inserting a new record into the products table we can see on fetch that the prices have indeed been re-calculated.
INSERT INTO Products (Name, Country, Price, ReleaseDate)
VALUES ('Smartphone', 'Netherlands', 1000, '2024-10-08');
INSERT INTO Products (Name, Country, Price, ReleaseDate)
VALUES ('Laptop', 'Germany', 2000, '2024-10-08');
INSERT INTO Products (Name, Country, Price, ReleaseDate)
VALUES ('Tablet', 'Spain', 500, '2024-10-08');Query #1
SELECT * FROM Products;| ProductID | Name | Country | Price | ReleaseDate |
|---|---|---|---|---|
| 1 | Smartphone | Netherlands | 1210.00 | 2024-10-08 |
| 2 | Laptop | Germany | 2380.00 | 2024-10-08 |
| 3 | Tablet | Spain | 550.00 | 2024-10-08 |
What would happen if the price of a product changes? Or when the product’s country changes? We could delete the record, and re-introduce the record with the updated price and country, so that the insert trigger can re-calculate the new price. However, a simpler solution could be to just introduce a BEFORE UPDATE trigger, where we re-calculate the costs according to the new value of the price.
DELIMITER $$
CREATE TRIGGER adjust_price_on_update
BEFORE UPDATE ON Products
FOR EACH ROW
BEGIN
-- Check if Country or Price has changed
IF NEW.Country != OLD.Country OR NEW.Price != OLD.Price THEN
-- Netherlands: Add 21% VAT
IF NEW.Country = 'Netherlands' THEN
SET NEW.Price = NEW.Price * 1.21;
-- Germany: Add 19% VAT
ELSEIF NEW.Country = 'Germany' THEN
SET NEW.Price = NEW.Price * 1.19;
-- France: Add 20% VAT
ELSEIF NEW.Country = 'France' THEN
SET NEW.Price = NEW.Price * 1.20;
-- Default VAT (e.g., 10%) for other countries
ELSE
SET NEW.Price = NEW.Price * 1.10;
END IF;
END IF;
END $$
DELIMITER ;
UPDATE Products set Price = 800, Country = 'France' WHERE ProductID = 1;Query #1
SELECT * FROM Products;| ProductID | Name | Country | Price | ReleaseDate |
|---|---|---|---|---|
| 1 | Smartphone | France | 960.00 | 2024-10-08 |
| 2 | Laptop | Germany | 2380.00 | 2024-10-08 |
| 3 | Tablet | Spain | 550.00 | 2024-10-08 |
As we can see, the price is adjusted accordingly.
Other use-cases
Briefly mentioned before, triggers have other merits aswell. One practical example is tracking the changes made in a table by creating a changelog. We could create triggers that keep track of the changes made to the products. For instance, you could create a Product_changes table where a trigger writes to, whenever changes are made to the Products table. The Product_changes table would contain columns prefixed with old_ and new_ for each column that exists in the Products table. Thus, if we would change the country of the first record in the Products table, the Product_changes table would properly reflect that. Other examples include validation or integrity checking, which can save you from some serious blunders, for example ensuring that the price is not negative, or not lower than a specific amount (e.g. ensuring that the discount of a product is at most 50%, in case someone accidentally updates the price while forgetting a digit. It can save you bankruptcy!).
Pros and cons
You might be thinking, great, triggers sound like a wonderful idea, let me implement it for my next cool project or propose it to my boss! However, before you get to it, there are still a few disadvantages that you should be aware of. First of all, it is a nightmare to troubleshoot triggers. The complexer the logic, the harder it is to pinpoint where during the execution the trigger goes wrong. Given that the triggers run in transactions (they roll-back on failure), it is hard to keep track of where during the execution the triggers might introduce unwanted behaviour. One thing you could do is to to take a note of the input that causes the trigger to result in faulty behaviour and to test it in an isolated environment, or go to through it line by line in the console.
Thus, it is often recommended to do as much logic as possible within the application code, and to keep the triggers as simple as possible. On top of that, given that triggers are basically just SQL statements that run before or after a CRUD operation, performance overhead is introduced. There are some ways to optimize the trigger performances, but overhead will always be there. One more thing is that triggers are hidden from the client-application; thus it might be harder to deduce the overall data flow and logic from a first glance.
Pros
- Tedious simple tasks that can introduce human-error can be automated.
- Data integrity and validation, ensure that the data you put into your table is valid.
- Monitor changes that occur in your tables.
Cons
- Difficult to troubleshoot.
- Performance overhead.
- Hidden from the client-applications, which makes it harder to maintain, as it is more difficult for developers to understand the overall data flow and logic without going into the database layer.
What's next?
In the next article we will go more in-depth on how to write a go application which also connects to a mysql table, how to define the triggers from the application-side, and how to do the migrations. We will also shortly discuss ways to improve the performance overhead. As always, stay curious, and feel free to leave any questions in the comments.
Stay Curious!
Example
Please see the working example here: DBFiddle