One of the topics I came across during this process was database triggers. Since I found them quite interesting and useful, I thought it would be helpful to share what I learned.
In this post, I’ll explain the basics of SQL triggers, especially in the context of relational databases, which I usually work with.
What Are Database Triggers?
A trigger is a special type of stored procedure that automatically runs when a specific event occurs in the database. Instead of executing manually, triggers are activated automatically when certain actions are performed on a table or database.
Triggers are commonly used for tasks like:
- Maintaining data integrity
- Logging changes
- Enforcing business rules
- Auditing database activities
Types of Database Triggers
Database triggers are generally categorized into three main types.
1. DML Triggers (Data Manipulation Language Triggers)
DML triggers are executed when data in a table is modified. These triggers fire when operations such as:
- INSERT
- UPDATE
DELETE
are performed on a table.
For example, you could use a DML trigger to automatically record changes whenever a row in a table is updated.
2. DDL Triggers (Data Definition Language Triggers)
DDL triggers are triggered when the structure of the database objects is modified.
These triggers occur when commands such as:
- CREATE
- ALTER
DROP
are executed on database objects like tables, views, or indexes.
DDL triggers are often used to monitor or control schema changes in a database.
3. System / Database Event Triggers
These triggers are associated with database-level events rather than table-level changes.
They are executed when certain system events occur, such as:
- Database logon
- Database logoff
- Database startup or shutdown
These triggers are useful for auditing user sessions or controlling access to the database.
How Triggers Work
Now that we have a basic understanding of what triggers are and the different types of triggers, let’s look at how triggers work in practice and how we can create them in SQL.
When creating a trigger, the first step is to create a function.
This function contains the logic that the trigger will execute automatically when a specific event happens.
To understand this better, let’s consider a simple example.
Suppose we have two tables:
- employee – stores employee details
- employee_records – stores logs whenever an employee is deleted or newly created
The employee table keeps the main employee data, while the employee_records table works like an activity log that records actions performed on employees.
Step 1 – Create the Tables
CREATE TABLE employee (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
department VARCHAR(100),
salary DECIMAL(10,2)
);
CREATE TABLE employee_records (
record_id SERIAL PRIMARY KEY,
emp_id INT,
emp_name VARCHAR(100),
action_time TIMESTAMP,
action VARCHAR(50)
);
| record_id | emp_id | emp_name | action_time | action |
|---|---|---|---|---|
| SERIAL (Primary Key) | INT | VARCHAR(100) | TIMESTAMP | VARCHAR(50) |
| record_id | emp_id | emp_name | action_time | action |
|---|---|---|---|---|
Step 2 – Insert Sample Data
INSERT INTO employee(emp_id, emp_name, department, salary) VALUES (1,'John','IT',50000), (2,'Amal','Finance',45000), (3,'Nimal','HR',40000), (4,'Saman','IT',48000);
| emp_id | emp_name | department | salary |
|---|---|---|---|
| INT (Primary Key) | VARCHAR(100) | VARCHAR(100) | DECIMAL(10,2) |
| emp_id | emp_name | department | salary |
|---|---|---|---|
| 1 | John | IT | 50000 |
| 2 | Amal | Finance | 45000 |
| 3 | Nimal | HR | 40000 |
| 4 | Saman | IT | 48000 |
Understanding OLD and NEW
Used mainly when performing DELETE or UPDATE operations.
Example:
- If we delete an employee, we use OLD
- If we insert a new employee, we use NEW
In our case, we are going to delete an existing employee with emp_id = 4, and we want that action to be automatically recorded in the employee_records table.
Step 3 – Create the Trigger Function
CREATE OR REPLACE FUNCTION emp_delete_log() RETURNS TRIGGER AS $$ BEGIN -- Insert a record into employee_records when an employee is deleted INSERT INTO employee_records(emp_id, emp_name, action_time, action) VALUES(OLD.emp_id, OLD.emp_name, CURRENT_TIMESTAMP, 'emp deleted'); RETURN OLD; END; $$ LANGUAGE plpgsql;
Explanation
- The function name is emp_delete_log()
- It inserts the deleted employee's details into employee_records
- We use OLD.emp_id and OLD.emp_name because the record already existed
- CURRENT_TIMESTAMP records when the action happened
- RETURN OLD returns the deleted record
The reason we use OLD here is because we are working with an existing record that is being deleted.
Step 4 – Create the Trigger
CREATE TRIGGER employee_delete_trigger AFTER DELETE ON employee FOR EACH ROW EXECUTE FUNCTION emp_delete_log();
Step 5 – Delete an Employee
DELETE FROM employee WHERE emp_id = 4;
| emp_id | emp_name | department | salary |
|---|---|---|---|
| 1 | John | IT | 50000 |
| 2 | Amal | Finance | 45000 |
| 3 | Nimal | HR | 40000 |
| record_id | emp_id | emp_name | action_time | action |
|---|---|---|---|---|
| 1 | 4 | Saman | 2026-03-05 10:30 | emp deleted |
So far, we learned how to log a record when an employee is deleted using a trigger.
But in real-world systems, we usually don’t allow operations to happen without rules. For example, we might want to prevent certain employees from being deleted or validate data before inserting it into the database.
Triggers help us enforce these business rules directly inside the database.
Let’s look at a few examples.
Prevent Deleting Employees from the IT Department
Suppose we want to prevent deleting employees who belong to the IT department.
To achieve this, we create a trigger function that checks the department before deletion.
If the employee belongs to the IT department, the database will raise an error and stop the deletion.
CREATE OR REPLACE FUNCTION emp_del_check() RETURNS TRIGGER AS $$ BEGIN IF OLD.department = 'IT' THEN RAISE EXCEPTION 'You cannot delete employee from IT department: Contact IT Department'; END IF; RETURN OLD; END; $$ LANGUAGE plpgsql;
Explanation
- OLD.department refers to the existing record that is about to be deleted
- If the department is IT, the database raises an exception
- The exception stops the delete operation
Step 2 – Create the Trigger
CREATE TRIGGER emp_delete_check_trigger BEFORE DELETE ON employee FOR EACH ROW EXECUTE FUNCTION emp_del_check();
Why Do We Use BEFORE DELETE?
We use BEFORE DELETE because we want to check the condition before the deletion happens.
If the condition fails, the trigger throws an error and prevents the deletion.
Validating Data Before Inserting Records
Step 1 – Create the Function
CREATE OR REPLACE FUNCTION before_insert_check() RETURNS TRIGGER AS $$ BEGIN IF NEW.salary < 10000 THEN RAISE EXCEPTION 'Adding employee with salary less than 10000 is not allowed'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;
Explanation
- NEW.salary refers to the salary value of the new employee being inserted
- If the salary is less than 10000, the trigger raises an error
- The record will not be inserted into the table
Step 2 – Create the Trigger
CREATE TRIGGER before_insert_check_trigger BEFORE INSERT ON employee FOR EACH ROW EXECUTE FUNCTION before_insert_check();
This trigger will run automatically before inserting a new employee record.
If the salary condition is not satisfied, the insertion will be blocked by the database.
Logging New Employee Insertions Using Triggers
So far, we have explored several trigger examples:
- Logging employee deletions
- Preventing deletions based on conditions
- Validating data before inserting
Now let’s look at another useful scenario.
What if we want to log whenever a new employee is added to the employee table, similar to how we logged deletions earlier?
We can achieve this by creating a trigger that records insert operations in the employee_records table.
Step 1 – Create the Function
CREATE OR REPLACE FUNCTION emp_add_logs() RETURNS TRIGGER AS $$ BEGIN INSERT INTO employee_records (emp_id, emp_name, action_time, action) VALUES (NEW.emp_id, NEW.emp_name, CURRENT_TIMESTAMP, 'new emp added'); RAISE NOTICE 'New employee inserted'; RETURN NEW; END; $$ LANGUAGE plpgsql;
Explanation
- NEW.emp_id and NEW.emp_name refer to the new employee record being inserted
- The trigger inserts those values into the employee_records table
- CURRENT_TIMESTAMP records the time when the employee was added
- RAISE NOTICE simply prints a message indicating the insertion
- RETURN NEW returns the inserted row
We use NEW here because the data is newly being inserted into the table.
Step 2 – Create the Trigger
CREATE TRIGGER emp_add_logs_trigger AFTER INSERT ON employee FOR EACH ROW EXECUTE FUNCTION emp_add_logs();
Why Do We Use AFTER INSERT?
Once the insertion is complete, the trigger records the action in the employee_records table.
In this article, we explored the basics of database triggers and how they can automate important tasks inside a database.
We looked at several practical examples, including:
- Creating trigger functions
- Logging employee deletions
- Preventing deletions using conditions
- Validating data before inserting records
- Logging new employee insertions
Triggers are powerful because they allow the database to automatically respond to events without requiring manual intervention from applications or users.
They are commonly used for:
- Auditing and logging
- Enforcing business rules
- Maintaining data integrity
- Automating database operations
However, triggers should be used carefully, as too many triggers can sometimes make a database harder to maintain or debug.
Overall, when used properly, triggers can be a very useful tool for building reliable and automated database systems.
