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 DBA and 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 set 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 triggers in SQL!
What Is a Trigger in SQL?
In SQL, a trigger is a database object containing SQL logic 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. As a result, 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. You can find more about sql db triggers in the dedicated documentation pages below:
Let's learn more about what types of SQL triggers are available.
Types of SQL Triggers
There are two types of triggers:
Time to dig into both!
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, 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 data 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.
Statement-level triggers are effective tools for enforcing business rules and maintaining data integrity across an entire table. Unlike row-level triggers, they are executed once per SQL statement, which makes them generally more efficient, particularly for operations affecting large volumes of data. On the other hand, they are less flexible and do not cover use cases where specific row-level actions are required.
How To Create an SQL Trigger
The syntax for creating an SQL trigger changes from one RDBMS to another. At the same time, the general ideas behind them are the same. In this section, you will see how to define 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 inside trigger_body. This is how a trigger works in SQL.
If you are 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.
Use Cases for SQL Triggers
Learn why and when SQL triggers are useful by taking a look at their three most common use cases!
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%:
1
CREATE TRIGGER enforce_price_rule
2
BEFORE INSERT OR UPDATE ON products
3
FOR EACH ROW
4
BEGIN
5
IF NEW.price < (NEW.cost * 1.10) THEN
6
SIGNAL SQLSTATE '45000'
7
SET MESSAGE_TEXT = 'Price must be at least 10% higher than the cost.';
8
END IF;
9
END;
You will learn how the above SQl trigger syntax works in the next chapter.
Note: The SIGNAL function is specific to MySQL. If you are using a different SQL dialect (e.g., PostgreSQL, SQL Server), the syntax might vary.
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 instance, you can define an SQL trigger to make sure a foreign key is copied in a summary table when a new record is inserted.
1
CREATE TRIGGER copy_foreign_key_to_summary
2
AFTER INSERT ON orders
3
FOR EACH ROW
4
BEGIN
5
INSERT INTO order_summary (order_id, customer_id, order_date)
6
VALUES (NEW.order_id, NEW.customer_id, NEW.order_date);
7
END;
Complete SQL Trigger Example
Let’s now see some a complete SQL trigger example. Note that SQL code below will be in MySQL, but you can easily adapt it to any other RDBMS technology.
The trigger will be defined and executed in DbVisualizer, the feature-rich database client with the highest user satisfaction on the market. That is a great MySQL database client tool!
Now, assume you want to log some data every time you create a new user. Here is how you can achieve that with 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 particular, it will log 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 launch the trigger creation query 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 statement, which temporarily changes the default semicolon ; delimiter used by MySQL to separate statements.
That is required because a CREATE TRIGGER query contains multiple semicolons ;. So, 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 %%%.
Equivalently, you can create a trigger in DbVisualizer by right-clicking on your table and selecting the “Create Trigger…” option:
Next, fill 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, 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 before.
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 and also search through them and update them 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 in SQL have an effect on the performance and data quality of your database, you need to deal with them carefully.
As shown here, 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
What are the different types of triggers in SQL Server?
The database triggers SQL Server types are:
What is the difference between a row-level trigger and a statement-level trigger?
A row-level trigger executes once for each row affected by a DML statement, making it ideal for operations that require detailed row-by-row processing. In contrast, a statement-level trigger executes only once per DML statement, regardless of the number of rows affected, making it more efficient for operations that apply broadly to a table. Row-level triggers offer finer granularity, while statement-level triggers are more performance-efficient for bulk operations.
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. Instead, MySQL only supports 6 combinations of triggers
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 an SQL trigger?
You can easily delete a trigger in SQL with the DROP TRIGGER statement below:
1
DROP TRIGGER trigger_name
Where trigger_name is the name of the trigger you want to drop.
How to update triggers 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. Another solution is to use a database client with full support for triggers, such as DbVisualizer.
