Types, Applications, How They Work, and More

SQL triggers are like automated routines in a database that execute predefined actions when specific events like INSERT, UPDATE, or DELETE occur in a table. This helps in automating data updation and setting some rules in place. It keeps the data clean and consistent without you having to write extra code every single time. In this article, we will look into what exactly an SQL trigger is and how it works. We will also explore different types of SQL triggers through some examples and understand how they are used differently in MySQL, PostgreSQL, and SQL Server. By the end, you will have a good idea about how and when to actually use triggers in a database setup.

What is an SQL Trigger?

A trigger is like an automatic program that is tied to a database table, and it runs the SQL code automatically when a specific event happens, like inserting, updating or deleting a row. For example, you can use a trigger to automatically set a timestamp on when a new row is created, added or deleted, or new data rules are applied without extra code in your application. In simple terms, we can say that a trigger is a stored set of SQL statements that “fires” in response to table events.

How Triggers Work in SQL

In MySQL, triggers are defined with the CREATE TRIGGER statement and are attached to a specific table and event. Each trigger is row-level, meaning it runs once for each row affected by the event. When you create a trigger, you specify:

• Timing: BEFORE or AFTER – whether the trigger fires before or after the event.
• Event: INSERT, UPDATE, or DELETE -the operation that activates the trigger.
• Table: the name of the table it’s attached to.
• Trigger Body: the SQL statements to execute, enclosed in BEGIN … END.

For example, a BEFORE INSERT trigger runs just before a new row is added to the table, and an AFTER UPDATE trigger runs right after an existing row is changed. MySQL requires the keyword FOR EACH ROW in a trigger, which makes it execute the trigger body for every row affected by the operation.

Inside a trigger, you refer to the row data using the NEW and OLD aliases. In an INSERT trigger, only NEW.column is available (the incoming data). Similarly, in a DELETE trigger, only OLD.column is available (the data about the row being deleted). However, in an UPDATE trigger, you can use both: OLD.column refers to the row’s values before the update, and NEW.column refers to the values after the update.

Let’s see trigger SQL syntax:

CREATE TRIGGER trigger_name
BEFORE|AFTER UPDATE ON table_name
FOR EACH ROW
BEGIN
-- SQL statements here --
END;

This is the standard SQL form. One thing which needs to be noted is that the trigger bodies often include multiple statements with semicolons; you should usually change the SQL delimiter first, for example to //, so the whole CREATE TRIGGER block is parsed correctly.

Step-by-Step Example of Creating Triggers

Now let’s see how we can create triggers in SQL.

Step 1: Prepare a Table

For this, let’s just create a simple users table:

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50),
created_at DATETIME,
updated_at DATETIME
);

Step 2: Change the Delimiter

In SQL, you can change the statement delimiter so you can write multi-statement triggers. For example:

DELIMITER //

Step 3: Write the CREATE TRIGGER Statement

For instance, we can create a trigger that sets the created_at column to the current time on insertion:

CREATE TRIGGER before_users_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
IF NEW.created_at IS NULL THEN
SET NEW.created_at = NOW();
END IF;
END;
//

So, in the above code, the BEFORE INSERT ON users means the trigger fires before each new row is inserted. The trigger body checks if NEW.created_at is null, and if so, fills it with NOW(). This automates setting a timestamp.

After writing the trigger, you can restore the delimiter if desired so that other codes can execute without any issues.

DELIMITER ;

Step 4: Test the Trigger

Now, when you insert without specifying created_at, the trigger will be set automatically.

INSERT INTO users (username) VALUES ('Alice');
SELECT * FROM users;

And the created_at will be filled automatically with the current date/time. A trigger can automate tasks by setting up default values.

Different Types of Triggers 

There are six types of SQL triggers for each table:

1. BEFORE INSERT trigger
2. BEFORE UPDATE Trigger
3. BEFORE DELETE Trigger
4. AFTER INSERT Trigger
5. AFTER UPDATE Trigger
6. AFTER DELETE Trigger

Let’s learn about each of them through examples.

1. BEFORE INSERT Trigger

This trigger is activated before a new row is inserted into a table. It is commonly used to validate or modify the data before it is saved.

Example of trigger SQL syntax for BEFORE INSERT:

DELIMITER //

CREATE TRIGGER before_insert_user
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SET NEW.created_at = NOW();
END;
// 

DELIMITER ;

This trigger is automatically set at the created_at timestamp to the current time before a new user record is inserted.

2. BEFORE UPDATE Trigger

This trigger is executed before an existing row is updated. This allows for validation or modification of data before the update occurs.

Example of trigger SQL syntax for BEFORE UPDATE:

DELIMITER //

CREATE TRIGGER before_update_user
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  IF NEW.email NOT LIKE '%@%' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid email address';
  END IF;
END;
//
 
DELIMITER ;

This trigger checks if the new email address is valid before updating the user record. If not, then it raises an error.

3. BEFORE DELETE Trigger

This is executed before a row is deleted. And can also be used for enforcing referential integrity or preventing deletion under certain conditions.

Example of trigger SQL syntax for BEFORE DELETE:

DELIMITER //

CREATE TRIGGER before_delete_order
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
  IF OLD.status="Shipped" THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete shipped orders';
  END IF;
END;
//
 
DELIMITER ;

This trigger prevents deletion of orders that have already been shipped.

4. AFTER INSERT Trigger

This trigger is executed after a new row is inserted and is often used for logging or updating related tables.

Example of trigger SQL syntax for AFTER INSERT

DELIMITER //

CREATE TRIGGER after_insert_user
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_logs(user_id, action, log_time
  VALUES (NEW.id, 'User created', NOW());
END;
//
 
DELIMITER ;

This trigger logs the creation of a new user in the user_logs table.

5. AFTER UPDATE Trigger

This trigger is executed after a row is updated. And is useful for auditing changes or updating related data.

Example of trigger SQL syntax for AFTER UPDATE

DELIMITER //

CREATE TRIGGER after_update_user
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_logs(user_id, action, log_time)
  VALUES (NEW.id, CONCAT('User updated: ', OLD.name, ' to ', NEW.name), NOW());
END;
//
 
DELIMITER ;

This trigger logs the change in a user’s name after an update.

6. AFTER DELETE Trigger

This trigger is executed after a row is deleted. And is commonly used for logging deletions or cleaning up related data.

Example of trigger SQL syntax for AFTER DELETE

DELIMITER //

CREATE TRIGGER after_delete_user
AFTER DELETE ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_logs(user_id, action, log_time)
  VALUES (OLD.id, 'User deleted', NOW());
END;
//
 
DELIMITER ;

This trigger logs the deletion of a user in the user_log table.

When and Why to Use Triggers

Triggers are powerful when you want to automate things that happen when the data changes. Below are some use cases and advantages highlighting when and why you should use SQL triggers.

• Automation of Routine Tasks: You can automate or auto-fill or update columns like timestamps, counters, or some calculated values without writing any extra code in your app. Like in the above example, we have used the created_at and updated_at fields automatically.
• Enforcing Data Integrity and Rules: Triggers can help you check conditions and even prevent invalid operations. For instance, a BEFORE_INSERT trigger can stop a row if it breaks some rules by raising an error. This makes sure that the data stays clean even if an error happens.
• Audit Logs and Tracking: They can also help you record changes automatically. An AFTER DELETE trigger can insert a record into a log table whenever a row is deleted. This gives an audit trail without having to write separate scripts.
• Maintaining Consistency Across Multiple Tables: Sometimes, you must have a situation where, when one table is changed, you want the other table to update automatically. Triggers can handle these linked updates behind the scenes.

Performance Considerations and Limitations

You must run triggers with care. As triggers run quietly every time data changes, they may sometimes slow things down or make debugging tricky, if you have too many. Still, for things like setting timestamps, checking inputs, or syncing other data, triggers are really useful. They save time and also reduce silly mistakes from writing the same code again and again.

Here are some points to consider before deciding to use SQL triggers:

• Hidden Logic: trigger code is stored in the databases and runs automatically, which can make the system’s behaviour less transparent. So, Developers might forget that the trigger is altering data behind the scenes. Therefore, it should be documented well.
• No Transaction Control: You cannot start, commit or roll back a transaction inside a SQL trigger. All trigger actions occur within the context of the original statement’s transaction. In other words, you can’t commit a partial change in a trigger and continue the main statement. 
• Non-transactional Tables: If you use a non-transactional engine and a trigger error may occur. SQL cannot fully roll back. So some parts of the data might change, and some parts might not, and this can make the data inconsistent.
• Limited Data Operations: SQL limits triggers from executing certain statements. For example, you cannot perform DDL or call a stored routine that returns a result set. Also, there are no triggers on views in SQL.
• No Recursion: SQL does not allow recursion; it can’t keep on modifying the same table on which it is defined in a way that would cause itself to fire again immediately. So it is advisable to avoid designing triggers that loop by continuously updating the same rows.

Comparison Table for MySQL vs PostgreSQL vs SQL Server Triggers

Let’s now have a look at how triggers differ on different databases such as MySQL, PostgreSQL, and SQL Server.

Login to continue reading and enjoy expert-curated content.

Keep Reading for Free