intro
In this article, you will learn what an SQL trigger is, what types of triggers exist, why SQL triggers are useful, and how to use one in a complete example.
SQL triggers are a powerful tool that every developer who deals with databases should know how to use. An SQL trigger allows you to specify SQL actions that should be executed automatically when a specific event occurs in the database. For example, you can use a trigger to automatically update a record in one table whenever a record is inserted into another table.
In this article, you will learn what SQL triggers are, how they work, and how to use them in your database. Follow this guide and become an expert on SQL triggers!
What Is a Trigger in SQL?
In SQL, a trigger is a database object containing SQL code that is automatically executed when a specific database event occurs. In other words, a database trigger is "triggered" by a particular event.
SQL triggers are generally associated with a particular table. This means that when the table is deleted, all its associated triggers are deleted accordingly. Given a table, an SQL trigger can be invoked before or after the following events:
Thus, when an INSERT, UPDATE, or DELETE SQL query is performed, the RDBMS takes care of automatically firing the corresponding trigger.
Let's learn more about what types of SQL triggers are available.
Types of SQL Triggers
There are two types of triggers: row-level triggers and statement-level triggers. Let’s dig into them.
Row-Level Triggers
A row-level trigger is executed once for each row affected by the triggering event, which is typically an INSERT, UPDATE, or DELETE statement.
For example, let’s assume you defined an INSERT trigger for a table. Then, you add several rows to that table with a single query. So, the row trigger will be automatically fired for each new row.
Row-level triggers are particularly useful to enforce business rules, maintain database integrity, and automatically perform SQL tasks. At the same time, they can have a significant impact on the performance of a database. This is especially true if they are used extensively or contain complex queries. So, you need to use row-level triggers only when really necessary.
Statement-Level Triggers
A statement-level trigger is executed once for the entire triggering event, instead of once for each row affected by the event. Statement-level triggers are useful to perform an action based on the overall effect of an INSERT, UPDATE, or DELETE statement, rather than on individual rows.
Use Cases for SQL Triggers
Let's now take a look at the three most common SQL trigger use cases. Learn why and when SQL triggers are useful.
Enforcing business rules
SQL triggers can be used to automatically enforce business rules at the database level. For example, a trigger can be used to ensure that the price of a product is never set to less than its cost + 10%.
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.
Automating tasks
Triggers are great for automating tasks in a database and avoiding performing scheduling tasks. For example, you can use SQL triggers to automatically log data, update a data aggregation table, or populate a user notification table.
Maintaining database integrity
Triggers can be used to ensure that data in a database remains consistent and accurate. For example, you can define an SQL trigger to make sure a foreign key is copied in a summary table when a new record is inserted.
How To Create a SQL Trigger
The syntax for creating a SQL trigger changes from one RDBMS to another. At the same time, the general ideas behind them are the same. In this blog post, you will see triggers in MySQL, but triggers in other database management systems work in the same way.
You can create a trigger in MySQL with the following CREATE TRIGGER statement:
1
CREATE
2
[DEFINER = user]
3
TRIGGER [IF NOT EXISTS] <trigger_name>
4
<trigger_time> <trigger_event>
5
ON <table_name> FOR EACH ROW
6
[<trigger_order>]
7
<trigger_body>
Where:
When <trigger_event> occurs on the <table_name> table, the <trigger_name> trigger will be executed on <trigger_time>. In detail, MySQL will run the SQL code. This is how a trigger works in SQL.
If you’re using MySQL, note that the FOR EACH ROW part of the CREATE TRIGGER syntax forces the trigger to be a row-level trigger. This is because MySQL only supports row-level triggers. In Oracle, you can create statement-level triggers by specifying FOR EACH STATEMENT instead of FOR EACH ROW.
Also, keep in mind that MySQL's OLD and NEW keywords provide access to the columns of the rows affected by a trigger. OLD gives you access to the row columns before the update, while NEW allows you to access the new column values.
SQL Trigger Examples
Let’s now see some SQL trigger examples. Note that all the examples below will be in MySQL, but you can easily adapt them to other RDBMS technologies.
Example of an INSERT trigger
Here is an example of a MySQL trigger that demonstrates how to create a SQL trigger that fires after an INSERT statement:
1
CREATE TRIGGER log_user_data
2
AFTER INSERT ON users
3
FOR EACH ROW
4
BEGIN
5
INSERT INTO user_creation_log(id, created_at, created_by)
6
VALUES (NEW.id, NOW(), NEW.created_by)
7
END;
This trigger will be fired whenever a new record is inserted into the users table. In detail, it automatically logs some data into the user_creation_log table. Note that NEW.id and NEW.created_by refer to the values of the id and created_by columns of the new row that was just inserted into the users table.
You can create this trigger in DbVisualizer as follows:
The “SUCCESS” status shown by DbVisualizer will help you understand that the operation was executed correctly and the trigger was created as expected.
Note the use of the DbVisualizer @delimiter command in the CREATE TRIGGER query. This command internally calls the MySQL DELIMITER command, which temporarily changes the default semicolon ; delimiter used by MySQL to separate statements. This is necessary because a CREATE TRIGGER query contains multiple semicolons ;. To execute the trigger creation statement, you must temporarily change the semicolon delimiter to a different delimiter. In the example provided below, the new delimiter is %%%
.
Similarly, you can create a trigger in DbVisualizer by right-clicking on your database, selecting the “Create trigger” option, and filling in the popup window below as required:
Then, click on the “Execute” button to create the trigger.
If you reconnect to your database, in the DbVisualizer “Triggers” dropdown menu, you will now have access to the log_user_data trigger. Here, you can see the trigger definition:
Now, whenever you create a new user record, some of its data will be logged in the user_creation_log table. For example, if you launch the following query:
1
INSERT INTO users(id, nickname, points, created_by)
2
VALUES (NULL, "test", 3100, 3);
user_creation_log will then contain the following data:
This record was created by the INSERT trigger defined above.
Et voilà! You just learned how to create an INSERT trigger with the help of DbVisualizer. Similarly, you can use DbVisualizer to create UPDATE and DELETE triggers. In detail, DbVisualizer also allows you to search through triggers, and update their query visually directly in a popup window.
Conclusion
Here, you learned:
SQL triggers are a powerful tool for automatically keeping data from your database up to date. Since triggers have an effect on the performance and data quality of your database, you need to deal with them carefully. In detail, adopting a database client that offers SQL trigger support, such as DbVisualizer, is one of the best ways to avoid headaches when dealing with triggers. Try DbVisualizer for free!
FAQ About SQL Triggers
What are the different types of triggers in SQL Server?
In SQL Server, there are four types of triggers:
How many types of triggers can be applied to a table?
The number of types of triggers that can be applied to a table depends on the RDBMS that is being used.
For example, in Oracle, you can apply up to 12 types of triggers to a table: 3 BEFORE EACH STATEMENT, 3 AFTER EACH STATEMENT, 3 BEFORE EACH ROW, and 3 AFTER EACH ROW. MySQL only supports 6 combinations of triggers, and other database management systems like ? supports ….
How many triggers can be applied to a table?
The number of triggers that can be applied to a table is typically limited by the maximum number of database objects supported by the RDBMS technology. In SQL Server, you can have 2,147,483,647 objects in a database. In contrast, given a table, older versions of RDBMSs generally allowed only one trigger per type. Note that this was not a real limitation, since a single trigger can perform several queries.
RDBMSs that support multiple triggers on a table generally provide the ability to define the order in which the triggers are executed. This way, you can control the flow in which the triggers are fired.
How to delete a SQL trigger?
You can easily delete a trigger in SQL with the DROP TRIGGER statement. Use it as follows:
1
DROP TRIGGER <trigger_name>
Where <trigger_name> is the name of the trigger you want to drop.
How to update a trigger in SQL?
In SQL Server, you can directly update a trigger with the ALTER TRIGGER statement. On the other hand, MySQL, Oracle, and PostgreSQL do not support trigger updates. If you want to update a trigger in MySQL or Oracle, you can use CREATE OR REPLACE. This special SQL statement allows you to create a new trigger or overwrite an existing one.
In all other RDBMSs that do not support CREATE OR REPLACE, such as PostgreSQL, to update a trigger you must delete it first and recreate one with the same name.
We hope you’ve enjoyed reading this article, learn more about database management systems and their functionality by familiarizing yourself with the DbVisualizer blog found here, and until next time!
