intro
This tutorial is designed to provide an introduction to triggers, so readers should expect to gain a basic understanding of the concept, as well as its use in databases. Users will learn what triggers are, how they work, and how to implement them in their own databases using SQL code and DbVisualizer. Additionally, they will gain hands-on experience writing triggers, with guided examples to help them practice.
Triggers are database objects that perform a predefined action on given tables whenever users modify data in a database. They are useful because they guarantee if a modification to the database was successful or not. If it was successful, then the trigger action will be executed.
Use Cases for Triggers
Triggers are used for a variety of use cases, such as:
Using triggers can improve database performance by letting us avoid the need to run manually written queries every time we modify data.
Triggers also provide an additional layer of data integrity and security by ensuring that data is valid and consistent and that only authorized users are able to modify the data. For example, the following trigger checks that the user is authorized before allowing any changes to be made to the database:
1
CREATE TRIGGER authorize_user
2
BEFORE UPDATE ON users
3
FOR EACH ROW
4
BEGIN
5
IF (NEW.user_id != OLD.user_id) THEN
6
IF (NEW.user_id NOT IN (SELECT user_id FROM authorized_users)) THEN
7
RAISE_ERROR 'Unauthorized user detected';
8
END IF;
9
END IF;
10
END;
The main disadvantage of using triggers is that they can be difficult to maintain, as any changes to the underlying tables or data can cause the triggers to malfunction. In the example below, a trigger is set up to update the total_orders column in the customers table whenever a new order is placed.
1
CREATE TRIGGER update_total_orders
2
AFTER INSERT ON orders
3
FOR EACH ROW
4
BEGIN
5
UPDATE customers
6
SET total_orders = total_orders + 1
7
WHERE customers.id = NEW.customer_id;
8
END;
If a column in the orders table is changed or removed, the trigger will no longer be able to update the customers table correctly, leading to incorrect data and malfunctioning of the system.
Another disadvantage is that triggers can be difficult to debug, as they are triggered by specific events and may not be easy to identify when a problem occurs. For example, a trigger is set up that should update a customer's address when they place an order, however, the address field is not updated when the order is placed. To debug this issue, the developer would need to identify which trigger is causing the issue. Is there a code error in the `update_customer_address` trigger or is the error in another trigger in the database which is preventing this one from running?
Creating a Trigger in DbVisualizer
To create a trigger in DbVisualizer, follow the steps below:
Once you have selected the “Create Trigger” option, the Create Trigger dialog will appear as shown in the screenshot below.
Now let’s get into an example of creating a trigger.
Trigger Example with DbVisualizer
Now that we have gone through the process of creating a trigger in DbVisualizer, let's take a look at an example of how to use a trigger to fire an action in the database. In this example, we have two tables; the Products table to store a list of products and the Products_quantity table to store the number of products in the first table. To make sure the number of products in the second table is updated, we will create a trigger that will update the Quantity column of the Products_quantity table when a new item is inserted into the table. To do this, we will need to create a trigger with the following parameters:
The trigger will fire the following action:
1
UPDATE
2
Products_quantity
3
SET
4
products = products + 1;
Once the trigger is written, you can save and activate it by clicking on the save icon or pressing CTRL + S. This will cause the trigger to be executed whenever a new product is inserted into the Products table.
You could also create the trigger in pure SQL by clicking on the Show SQL checkbox and entering the following code in the SQL editor:
1
CREATE TRIGGER
2
`ContactManager`.Update_Quantity AFTER
3
INSERT
4
ON
5
`ContactManager`.`Products` FOR EACH row BEGIN
6
Products_quantity
7
SET
8
products = products + 1;
9
END;
This trigger will increase the quantity field in the Products_quantity table by 1 when a new product is added to the Products table. Once the quantity has been increased, the trigger will stop running. The @delimeter command is used to signify the start and the end of the trigger code.
Recursive Triggers
A recursive trigger is a type of trigger that can fire multiple times in response to a single event. This can be useful for tasks such as cascade deletes, where a record is deleted from one table and then all related records in other tables must also be deleted.
In order to create a recursive trigger, the trigger body must include a statement that calls the trigger itself. This statement must include a condition that will prevent the trigger from running forever. The condition might be that the trigger should only run when the number of times it has been called is less than a certain number.
If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.
For example, when a record is deleted from a customer table we also want to delete all related records in other tables. This means our trigger will be attached to the `Customers` tables. In this example, we will be deleting related records from just the `Orders` table, but you could delete them from as many tables as you like.
The trigger action will look like this:
1
DELETE FROM orders WHERE customer_id = OLD.customer_id;
2
IF (SELECT COUNT(*) FROM Orders WHERE customer_id = OLD.customer_id) > 0
3
THEN CALL DeleteOrders(OLD.customer_id);
4
END IF;
This trigger will delete all orders related to the customer whose record has been deleted in the customer table. The IF statement checks to see if there are any records left in the orders table for the customer, and if there are, the trigger calls itself to delete them. Once all of the related orders have been deleted, the trigger will stop running.
Conclusion
DbVisualizer provides an interface for database triggers, making creating and managing them easier. You can learn more about triggers in DbVisualizer in their official documentation. In this tutorial, you learned about triggers, how they work, their advantages, disadvantages, and use cases. You also learned how to implement them in your SQL database. All in a good day’s work.
Reach out to me on LinkedIn if you have any questions or just want to connect, return to our blog to learn more about databases and their functionality later on, and until next time!
