Last modified: May 06, 2025
This article is written in: 🇺🇸
Welcome back to our exploration of SQL! Today, we're delving into the world of triggers, a powerful feature that allows you to automate actions in response to specific events in your database. Triggers can help maintain data integrity, enforce business rules, and keep an audit trail of changes—all without manual intervention.
After reading the material, you should be able to answer the following questions:
A trigger is a special kind of stored procedure that automatically executes, or "fires," in response to certain events on a table or view in a database. These events can be actions like inserting, updating, or deleting records. By defining triggers, you can specify custom behavior that occurs immediately before or after these events, ensuring your data remains consistent and adheres to your business logic.
Triggers can be categorized based on their timing and the events that activate them. Here's a detailed breakdown:
To create a trigger, you'll use the CREATE TRIGGER
statement. The exact syntax may vary slightly between different SQL dialects (such as MySQL, PostgreSQL, or SQL Server), but the general structure is similar.
CREATE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}
ON table_name
[FOR EACH ROW]
[WHEN (condition)]
BEGIN
-- SQL code to execute
END;
trigger_name
serves as a unique identifier for the trigger within the database, helping to distinguish it from others.Timing
specifies when the trigger executes in relation to the event, such as BEFORE
, AFTER
, or INSTEAD OF
.Event
defines the operation that activates the trigger, which can be an INSERT
, UPDATE
, or DELETE
statement.table_name
indicates the table or view with which the trigger is associated, restricting its scope to operations on this entity.FOR EACH ROW
clause specifies that the trigger operates at the row level, executing for each affected row in the triggering statement.WHEN (condition)
clause is optional and allows defining a condition that must be met for the trigger to fire, adding flexibility to trigger execution.BEGIN...END
block contains the SQL statements or logic that will execute when the trigger is activated, encapsulating the desired actions.Let's create a trigger that automatically sets the created_at
timestamp before a new record is inserted into the Users
table.
Step 1: Assume the Users Table
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Username VARCHAR(50),
CreatedAt DATETIME
);
Step 2: Create the Trigger
CREATE TRIGGER SetCreatedAt
BEFORE INSERT
ON Users
FOR EACH ROW
BEGIN
SET NEW.CreatedAt = NOW();
END;
SetCreatedAt
is the name assigned to the trigger, used for identifying it in the database schema.BEFORE INSERT
clause specifies that the trigger will execute prior to any INSERT
operation on the target table.ON Users
indicates that the trigger is bound to the Users
table, applying only to operations on this table.FOR EACH ROW
ensures the trigger executes for every row being inserted during the INSERT
operation.NEW.CreatedAt
references the CreatedAt
field of the new row being inserted into the table, allowing its value to be set or modified.NOW()
function fetches the current date and time, typically used to populate the CreatedAt
field with a timestamp reflecting when the row was inserted.Suppose we want to log changes made to the Employees
table into an Employees_Audit
table.
Step 1: Create the Audit Table
CREATE TABLE Employees_Audit (
AuditID INT PRIMARY KEY AUTO_INCREMENT,
EmployeeID INT,
ChangedAt DATETIME,
OldSalary DECIMAL(10, 2),
NewSalary DECIMAL(10, 2)
);
Step 2: Create the Trigger
CREATE TRIGGER LogSalaryChange
AFTER UPDATE
ON Employees
FOR EACH ROW
WHEN (OLD.Salary <> NEW.Salary)
BEGIN
INSERT INTO Employees_Audit (EmployeeID, ChangedAt, OldSalary, NewSalary)
VALUES (NEW.EmployeeID, NOW(), OLD.Salary, NEW.Salary);
END;
LogSalaryChange
is the specific name given to the trigger, which helps identify it within the database.AFTER UPDATE
clause ensures that the trigger fires only after an UPDATE
operation is successfully executed on the target table.WHEN (OLD.Salary <> NEW.Salary)
condition specifies that the trigger will execute only when there is a change in the Salary
column between the old and new row values.OLD
and NEW
keywords allow access to the values of the row before and after the UPDATE
operation, respectively, facilitating comparison or data tracking.Employees_Audit
table is used as a log to store information about salary changes, creating a record of modifications for auditing or tracking purposes.If you need to change the logic inside a trigger, you typically have to drop and recreate it. However, some SQL dialects support the ALTER TRIGGER
statement.
ALTER TRIGGER LogSalaryChange
ON Employees
AFTER UPDATE
AS
BEGIN
-- Updated SQL code
END;
Note: Always check your database's documentation to confirm whether ALTER TRIGGER
is supported and the exact syntax to use.
To remove a trigger from the database, you use the DROP TRIGGER
statement.
DROP TRIGGER IF EXISTS SetCreatedAt;
IF EXISTS
clause can be used when dropping a trigger to avoid errors if the specified trigger does not exist in the database. This provides a safeguard for scripts that may run multiple times or in environments where the trigger's existence is uncertain.SetCreatedAt
refers to the name of the specific trigger that is being dropped. This name must match the trigger as defined in the database schema for the DROP TRIGGER
statement to execute correctly.When working with triggers, it's important to understand the context in which they execute:
NEW
keyword is used within INSERT
and UPDATE
triggers to refer to the new row being added or modified in the table. This allows access to the new values being inserted or updated.OLD
keyword is used within UPDATE
and DELETE
triggers to refer to the existing row before the update or deletion operation takes place. This provides access to the original values prior to any changes.These pseudo-records allow you to access the values before and after the triggering event.
Suppose we have a BankAccounts
table, and we want to prevent any operation that would result in a negative balance.
Create the Trigger:
CREATE TRIGGER PreventNegativeBalance
BEFORE UPDATE
ON BankAccounts
FOR EACH ROW
BEGIN
IF NEW.Balance < 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Balance cannot be negative.';
END IF;
END;
BEFORE UPDATE
clause specifies that the trigger will execute before any update operation on the target table, allowing it to validate or modify data prior to the update.IF NEW.Balance < 0
evaluates whether the new balance being set is negative, enforcing a business rule or data constraint.SIGNAL
statement is used to raise a specific error if the condition is met, effectively halting the update operation and preventing the invalid data from being committed to the database.CHECK
, UNIQUE
, and FOREIGN KEY
, offer built-in mechanisms to enforce data integrity without the need for custom triggers.Let's build a practical example to illustrate how triggers can maintain an audit trail.
Scenario: We have a Products
table, and we want to keep a history of price changes.
Step 1: Create the Products Table
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Price DECIMAL(10, 2)
);
Step 2: Create the Products_PriceHistory Table
CREATE TABLE Products_PriceHistory (
HistoryID INT PRIMARY KEY AUTO_INCREMENT,
ProductID INT,
ChangedAt DATETIME,
OldPrice DECIMAL(10, 2),
NewPrice DECIMAL(10, 2),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
Step 3: Create the Trigger
CREATE TRIGGER RecordPriceChange
AFTER UPDATE
ON Products
FOR EACH ROW
WHEN (OLD.Price <> NEW.Price)
BEGIN
INSERT INTO Products_PriceHistory (ProductID, ChangedAt, OldPrice, NewPrice)
VALUES (NEW.ProductID, NOW(), OLD.Price, NEW.Price);
END;
Products_PriceHistory
.Price
column changes.