Syntax, examples, and use cases

A SQL trigger is a piece of logic that runs automatically when data in a table changes. It fires on INSERT, UPDATE, or DELETE events.

Sometimes you need your database to react to data changes. A new row is inserted, and you want to log it. A record gets updated, and you need a timestamp. A row is deleted, and something else should happen automatically.

This is what a SQL trigger does. It runs automatically, without you having to call it.

How SQL Triggers Work

Imagine you have a web app and a mobile app that both let users update their profiles, and both write to the same database. Now let’s say you want to log every time a user updates their profile.

Without motivationsyou’ll have to write that logging code twice: once in your web app, once in your mobile app. And if you later add a third way to update the database, you’ll need to remember to add it there as well.

With motivationsyou write the logic once inside the database itself. It fires automatically, every single time, no matter where the change comes from. Your web app, your mobile app, a script, or something else.

Each trigger follows the same pattern:

Event → Timing → Action

• event: Insert, update, or delete.
• Timing: BEFORE (before the change is written) or AFTER (once it is done)
• Process: Your SQL logic runs.

SQL trigger syntax

Before we jump into a full example, here’s what the basic syntax looks like in MySQL. MySQL is one of the most widely used database systems and an excellent starting point for learning SQL. Think of it as a template that you’ll fill in with your own logic.

CREATE TRIGGER trigger_name 
AFTER UPDATE 
ON table_name 
FOR EACH ROW 
BEGIN   
-- your logic here 
END; 

Each part has a function:

• trigger_name: What do you call it?
• AFTER UPDATE: when and what fires (can be time BEFORE or AFTERand may occur INSERT, UPDATEor DELETE)
• ON table_name: Which table to view.
• FOR EACH ROW: It runs once per affected row.
• BEGIN...END: wraps your logic.

SQL Trigger Example (Step by Step)

Let’s go through a real example. We will log every update. users table automatically.

Step 1: Create the tables

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  email VARCHAR(100),
  updated_at DATETIME
);

CREATE TABLE user_logs (
  log_id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  changed_at DATETIME
);

Step 2: Create the trigger

Now we will create the trigger that sees. users The table and logs a record whenever a row is updated.

CREATE TRIGGER log_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_logs (user_id, changed_at)
  VALUES (OLD.id, NOW());
END;

What are OLD And NEW?

When a row is updated, OLD Refers to row values. First Change, and NEW Refers to values After Change

In this trigger example, we use OLD.id To get the ID of the row that was just updated. NOW() unrelated to NEW. This is a built-in MySQL function that returns the current date and time. We will see. NEW In the action in the following example, where it is used to validate incoming data before saving it.

Step 3: Enter some data.

INSERT INTO users (name, email) VALUES ('Jake', '(email protected)');

Step 4: Run an update.

This update is what fires the trigger.

UPDATE users SET email = '(email protected)' WHERE id = 1;

Step 5: Check the result.

SELECT * FROM user_logs;

The trigger went off automatically. No additional application code is required.

Types of SQL Triggers

There are a few ways in which motivations can vary. You don’t have to memorize them all, but it helps to recognize them.

The syntax and behavior may differ slightly between database systems, but the basic idea is the same.

DML vs DDL Triggers

DML (Data Manipulation Language) Triggers fire on data changes (INSERT, UPDATE, DELETE). This is the one you will almost always use, and the only type MySQL supports.

DDL (Data Definition Language) Triggers fire on structural changes such as creating or modifying a table. These are available in other database systems eg SQL Server but are not supported in MySQL.

BEFORE vs. AFTER triggers

BEFORE triggers fire before the change is written, which is useful for validating or modifying data before it lands. AFTER triggers run once after a change, which is useful for logging or synchronizing related tables.

Row-level vs. statement-level triggers

The row-level trigger runs once per affected row. Update 10 rows, and it fires 10 times. A statement-level trigger runs once per query, regardless of how many rows are affected. MySQL only supports the row level. PostgreSQL (another popular database system, like MySQL) supports both.

Login triggers

Login Triggers fire when a user logs into the database. They are primarily used by database administrators to track logins, control access, or limit the number of active sessions. If you’re a beginner, you’re unlikely to need them, but it’s good to know they exist.

Here is a simple SQL Server example to illustrate the syntax.

CREATE TRIGGER track_logon
ON ALL SERVER
FOR LOGON
AS
BEGIN
    PRINT 'A new user has logged in.';
END;

Note that login triggers are specific to SQL Server and are not available in MySQL.

Common use cases

These are short examples that show only the logic that goes inside the BEGIN…END block of the trigger. They are not meant to run on their own.

Audit logs

Save records without relying on your app to do it every time something changes.

-- inside a trigger
INSERT INTO audit_log (table_name, action, changed_at)
VALUES ('users', 'UPDATE', NOW());

Data validation

Prevent corrupted data before it is saved. This example rejects any age less than zero and returns a custom error message.

-- inside a trigger
IF NEW.age < 0 THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age cannot be negative';
END IF;

SIGNAL SQLSTATE '45000' MySQL has a way to throw a custom error. gave '45000' is a common error code that means “user-defined error,” and MESSAGE_TEXT is the message that comes back.

Automatic updating fields

Put a field like updated_at existing (code outside of your database) without writing additional logic in your application code.

-- inside a BEFORE trigger
SET NEW.updated_at = NOW();

Synchronization of relational tables

When a row in one table changes, automatically update the other.

-- inside a trigger
UPDATE orders SET status = 'closed' WHERE user_id = OLD.id;

When to Use Triggers (and When Not to)

Use a trigger when:

• You need something to happen automatically on an INSERT, UPDATE, or DELETE, either every time (like logging) or conditionally (like validation).
• Multiple applications or scripts write to the same database and you need logic applied consistently regardless of the source.
• You want to enforce data integrity rules at the database level rather than relying on application code.

Avoid triggers when:

• The logic is complex or likely to change frequently.
• You need to be easy to debug and trace.
• The write volume and performance issues in the table are huge.
• The logic needs to be visible to anyone reading your application code, because triggers are invisible to your app layer and teammates may not realize one exists.
• You are concerned about cascading updates, which can happen if you modify the triggers tables that have their own triggers.

Managing SQL Triggers

Release a trigger.

In SQL, to “drop” something means to delete it. To remove the trigger completely:

DROP TRIGGER trigger_name;

See all motivations

SHOW TRIGGERS;

To view triggers on a specific table:

SHOW TRIGGERS FROM database_name LIKE 'table_name';

In MySQL, you cannot disable a trigger without releasing it. If you need to temporarily stop someone from firing, you have to knock it down and rebuild it if necessary.

wrap up

Triggers are the right tool when your database needs to react to changes automatically and continuously. Whether you’re logging updates to the Users table, validating data before landing it, or keeping related tables in sync, triggers let you handle this logic once at the database level instead of repeating it in your application code.

To brush up on your SQL skills, you can explore these SQL courses:

Both focus on courses. Hands-on, interactive learning. You execute SQL directly in your browser, get guided feedback, and work through realistic scenarios.

You’ll apply what you learn to real-world scenarios in queries, joins, and database design, so SQL becomes a functional skill rather than just memorizing syntax.

Frequently Asked Questions

What is a real-life example of a trigger in SQL?

A common example is logging changes to a table, such as tracking updates or deletions for auditing purposes.

Another example is to automatically update the timestamp when a record changes. A trigger can be set when a user updates their profile. updated_at to the present time without the need for any additional questions.

What is the purpose of a SQL trigger?

The purpose of a trigger is to automatically execute logic when data changes.

Common use cases include logging changes, validating data, and keeping related tables in sync.

Do triggers slow down the database?

Yes, they can.

Triggers execute each. INSERT, UPDATEor DELETEso complex or inefficient triggers can affect performance, especially on high-traffic tables.

Can the SQL trigger fail?

yes

If an error occurs in a trigger, depending on the database system, the original operation is usually canceled or rolled back.

How do I get a list of triggers in SQL?

You can list triggers using the following commands depending on your database.

MySQL: SHOW TRIGGERS;

PostgreSQL: SELECT * FROM information_schema.triggers;

SQL Server: SELECT * FROM sys.triggers;

Can you have multiple triggers on the same table?

yes

Most database systems, including MySQL (since version 5.7.2) and SQL Server, allow multiple triggers on the same table and event.

In MySQL, if multiple triggers share the same time and event, you can use this to control the order of execution. FOLLOWS And PRECEDES In the definition of motivation.

Can you disable the trigger in SQL?

In SQL Server, yes.

In MySQL, there is no option to disable the trigger. You need to release the trigger and recreate it if needed.

Can a trigger call another trigger?

Yes it is known as Trigger chaining or Cascading triggers.

For example, a trigger on one table can update another table, which then automatically fires its own trigger.

This can get complicated quickly, so careful management is important when working with multiple triggers.