MySQL
SQL
SQL SERVER
TRIGGERS

SQL Triggers: What They Are and How to Use Them

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:

  • INSERT: A new row is inserted in the table.
  • UPDATE: An existing row of the table gets updated.
  • DELETE: A row in the table gets deleted.

Thus, when an INSERTUPDATE, 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: 

  1. Row-level triggers
  2. Statement-level 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 INSERTUPDATE, 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 INSERTUPDATE, 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:

Copy
        
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:

  • trigger_name is the name given to the trigger.
  • trigger_time can have two values—BEFORE or AFTER—they define when the trigger should be invoked.
  • trigger_event can be INSERT, UPDATE, or DELETE and defines the database event that fires the trigger.
  • table_name is the table to associate with the trigger.
  • trigger_order has FOLLOWS | PRECEDES syntax and allows you to define the order of execution of triggers in a table in case there are multiple triggers.
  • trigger_body is the SQL code that is executed when the trigger is invoked.

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%:

Copy
        
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.

Copy
        
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:

Copy
        
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:

Launching the trigger creation query in DbVisualizer
Launching the trigger creation query in DbVisualizer

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:

Selecting the “Create Trigger…” option in DbVisualizer
Selecting the “Create Triggeru2026” option in DbVisualizer

Next, fill in the popup window below as required:

The trigger creation popup in DbVisualizer
The trigger creation popup in DbVisualizer

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:

Viewing a trigger in DbVisualizer
Viewing a trigger in DbVisualizer

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:

Copy
        
1 INSERT INTO users(id, nickname, points, created_by) 2 VALUES (NULL, "test", 3100, 3);

user_creation_log will then contain the following data:

The log row was created by the trigger
The log row was created by the trigger

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:

  • What an SQL trigger is.
  • The difference between row-level triggers and statement-level triggers.
  • How triggers work in SQL.
  • How to create INSERTUPDATE, and DELETE triggers.

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:

Copy
        
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.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

MySQL Backup and Recovery Best Practices: A Guide

author Lukas Vileikis tags BIG DATA MySQL OPTIMIZATION SECURITY SQL 7 min 2024-10-15
title

SQL Not Equal Operator: Definitive Guide with Examples

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-10-14
title

MySQL DISTINCT Clause Explained: Why, How & When

author Lukas Vileikis tags BEGINNER MySQL SQL 7 min 2024-10-10
title

SQL Server Date Diff: Calculating the Difference Between Two Dates

author Antonello Zanini tags SQL SERVER 6 min 2024-10-07
title

Counter in MySQL: Counting Rows with Ease

author Lukas Vileikis tags MySQL SQL 8 min 2024-10-03
title

A Complete Guide to the SQL Server FOR XML PATH Clause

author Antonello Zanini tags SQL SERVER XML 8 min 2024-10-01
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30
title

The MySQL RENAME COLUMN Statement: How, What, and Why

author Lukas Vileikis tags MySQL SQL 6 min 2024-09-26
title

SQL FETCH: Retrieving Data In Database Cursors

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-09-24
title

A Complete Guide to the SQL Server COALESCE Function

author Antonello Zanini tags SQL SERVER 6 min 2024-09-23

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗