Triggers y Vistas Materializadas en MySQL (Kinda)

Creating materialized views in MySQL is a powerful technique for keeping aggregated data automatically updated. Although MySQL does not offer this functionality natively, we can implement it using triggers, which allows us to keep summary tables synchronized with our main tables without manual intervention.

How to implement materialized views in MySQL?

When we need to keep summary tables automatically updated, such as daily sales statistics, we face a challenge: MySQL does not have native materialized views. However, we can create a solution using triggers that keep our summary tables synchronized with the source tables.

In this case, we have a table sales_daily_m that contains daily summaries of transactions and sales totals. The problem is that every time we update the bill_products source table, we must manually update our summary table.

Manual update of the summary table

Let's first see how the manual update works. If we insert a new record into bill_products:

INSERT INTO bill_products (bill_id, product_id, price, date_added)VALUES (1, 10, 23.98, '2025-03-11 11:23:43');

After this insert, we need to manually update our summary table. We can do this with an INSERT ... ON DUPLICATE KEY UPDATE:

INSERT INTO sales_daily_m (date, count, total)SELECT DATE(date_added), COUNT(id), SUM(total)FROM bill_productsGROUP BY 1ON DUPLICATE KEY UPDATEcount = (SELECT COUNT(*) FROM bill_products WHERE DATE(date_added) = date),total = (SELECT SUM(total) FROM bill_products WHERE DATE(date_added) = date);

This approach works, but requires manual intervention each time there are changes to the source table.

Automation with triggers

Triggers are functions that are automatically executed before or after certain events in a table. We can use them to keep our summary table updated automatically.

For our case, we will create a trigger that runs after every insert in the bill_products table:

DELIMITER |CREATE TRIGGER matview_insertAFTER INSERT ON bill_productsFOR EACH ROWBEGIN INSERT INTO sales_daily_m (date, count, total) VALUES ( DATE(NEW.date_added), (SELECT COUNT(*) FROM bill_products WHERE DATE(date_added) = DATE(NEW.date_added)), (SELECT SUM(total) FROM bill_products WHERE DATE(date_added) = DATE(NEW.date_added)) ) ON DUPLICATE KEY UPDATE count = VALUES(count), total = VALUES(total);END|DELIMITER ;

This trigger does the following:

1. It runs automatically after each insertion into bill_products.
2. For each inserted row, it updates the table sales_daily_m
3. If the date already exists in the summary table, it updates the count and total.
4. If the date does not exist, it creates a new record.

Important considerations about triggers

When working with triggers in MySQL, we must take into account some particularities:

1. Special syntax: The syntax of triggers includes the use of DELIMITER to temporarily change the command delimiter, which allows defining the trigger body with multiple instructions.

2. Special variables: Within the trigger, we can access the data of the row that activated the trigger through the NEW (for INSERT and UPDATE) and OLD (for UPDATE and DELETE) variables.

3. Error handling: If a trigger fails, the operation that activated it will also fail. This is important because it guarantees the integrity of our data.

4. Debugging: Errors in triggers are only detected during execution, not during creation, which can make debugging difficult.

What are the advantages of triggers for maintaining materialized views?

Triggers provide us with several advantages for implementing materialized views:

• Automation: Updates occur automatically without manual intervention.
• Consistency: Data in the summary table is always synchronized with the source table.
• Performance: Queries to the summary table are faster than calculating aggregates in real time.
• Flexibility: We can customize the update logic according to our specific needs.

Types of triggers according to the event

We can create triggers for different events:

• AFTER INSERT: Runs after inserting new records.
• AFTER UPDATE: Executed after updating existing records.
• AFTER DELETE: It is executed after deleting records.

There are also BEFORE triggers, which are executed before the event occurs, but for our case of materialized views, AFTER triggers are more appropriate.

Triggers are a powerful tool for keeping summary tables automatically updated in MySQL, allowing us to implement functionality similar to the materialized views that exist in other database management systems. Although their syntax can be a bit complex, the benefits they offer in terms of automation and performance make this technique worth mastering. Have you used triggers in your projects? Share your experience in the comments.