DbVisualizer

Posted on Mar 10, 2023 • Originally published at dbvis.com

SQL Triggers: What They Are and How to Use Them

#triggers

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:

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

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:

CREATE
    [DEFINER = user]
    TRIGGER [IF NOT EXISTS] <trigger_name>
    <trigger_time> <trigger_event>
    ON <table_name> FOR EACH ROW
    [<trigger_order>]
    <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_body> 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. 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:

CREATE TRIGGER log_user_data
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_creation_log(id, created_at, created_by)
    VALUES (NEW.id, NOW(), NEW.created_by)
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.

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

Then, click on the “Execute” button to create the trigger.

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:

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

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:

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

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:

DDL (Data Definition Language) triggers: fired by specific database events related to the definition of database objects, such as creating, altering, or dropping tables or stored procedures.
DML (Data Modification Language) triggers: triggered by certain database events related to the data modification, such as inserting, updating, or deleting rows in a table.
CLR (Common Language Runtime) triggers: implemented using C# or VB.NET to perform tasks that are not possible with T-SQL triggers, such as sending emails.
Logon triggers: triggered by the logon events of SQL Server users. They can be used to enforce security policies or to audit logon activity.

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:

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!

About the author

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

DEV Community