SQL Triggers are one of the most important advanced features of relational database systems. A trigger allows a database to automatically perform specific actions when certain events occur on a table. These actions are executed automatically without requiring the user or application to manually call them.
In modern applications, databases handle thousands of operations every second. Maintaining data accuracy, security, and consistency manually can become difficult. SQL Triggers help automate important database tasks such as maintaining audit records, validating data, updating related tables, and enforcing business rules.
Triggers are widely used in banking systems, e-commerce applications, healthcare software, educational portals, and enterprise-level database applications where automatic actions are required.
An SQL Trigger is a special type of stored program that automatically executes when a specific event occurs in a database table.
Unlike normal SQL statements, triggers do not execute directly by users. They are activated automatically by database events such as INSERT, UPDATE, or DELETE operations.
A trigger is an automatic database action that runs before or after a specific operation is performed on a table.
Triggers are created to automate repetitive database operations and maintain database integrity.
Some common reasons for using triggers are:
A trigger works based on three main components:
| Component | Description |
|---|---|
| Event | The operation that activates the trigger such as INSERT, UPDATE, or DELETE. |
| Condition | The situation when the trigger should execute. |
| Action | The SQL statements executed automatically. |
For example, when a new employee is added to an Employee table, a trigger can automatically insert the employee creation details into an Audit table.
The general syntax of creating a trigger is:
CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
BEGIN
-- SQL statements
END;
Where:
SQL triggers are mainly classified based on timing and database operations.
| Type | Description |
|---|---|
| BEFORE Trigger | Executes before the database operation. |
| AFTER Trigger | Executes after the database operation. |
| INSERT Trigger | Runs when new records are inserted. |
| UPDATE Trigger | Runs when existing records are modified. |
| DELETE Trigger | Runs when records are deleted. |
A BEFORE Trigger executes before an INSERT, UPDATE, or DELETE operation is completed.
It is commonly used for data validation and modifying values before they are stored in the database.
CREATE TRIGGER check_salary BEFORE INSERT ON Employee FOR EACH ROW BEGIN IF NEW.salary < 0 THEN SET NEW.salary = 0; END IF; END;
This trigger prevents negative salary values from being inserted into the Employee table.
An AFTER Trigger executes after the database operation has successfully completed.
These triggers are commonly used for maintaining logs and updating related information.
CREATE TRIGGER employee_log AFTER INSERT ON Employee FOR EACH ROW BEGIN INSERT INTO Employee_Audit VALUES (NEW.id, NEW.name, NOW()); END;
Whenever a new employee is added, the trigger automatically stores the details in the audit table.
An INSERT Trigger is executed whenever a new record is added to a table.
It is useful when additional actions are required after inserting data.
In an online shopping system, when a new customer registers, a trigger can automatically create a welcome record in another table.
CREATE TRIGGER customer_register AFTER INSERT ON Customer FOR EACH ROW BEGIN INSERT INTO Customer_Log VALUES (NEW.customer_id, 'New Customer Added'); END;
An UPDATE Trigger automatically executes when existing data in a table is modified.
It is useful for tracking changes and maintaining historical information.
CREATE TRIGGER salary_update AFTER UPDATE ON Employee FOR EACH ROW BEGIN INSERT INTO Salary_History VALUES (OLD.id, OLD.salary, NEW.salary); END;
This trigger stores previous and updated salary values whenever an employee salary changes.
A DELETE Trigger runs automatically when records are removed from a table.
It is commonly used to maintain backup information before deleting important records.
CREATE TRIGGER employee_delete BEFORE DELETE ON Employee FOR EACH ROW BEGIN INSERT INTO Deleted_Employee VALUES (OLD.id, OLD.name); END;
Before deleting an employee, the trigger stores the employee information in another table.
SQL provides special keywords to access old and new values during trigger execution.
| Keyword | Purpose |
|---|---|
| NEW | Contains the new value after INSERT or UPDATE. |
| OLD | Contains the previous value before UPDATE or DELETE. |
UPDATE Employee SET salary = 50000 WHERE id = 101;
OLD.salary contains the previous salary value and NEW.salary contains the updated salary value.
A Row-Level Trigger executes once for every row affected by a database operation.
For example, if an UPDATE statement modifies 100 records, the row-level trigger executes 100 times.
CREATE TRIGGER update_time AFTER UPDATE ON Employee FOR EACH ROW BEGIN UPDATE Employee SET Updated_Date = NOW(); END;
A Statement-Level Trigger executes only once for the complete SQL statement, regardless of the number of rows affected.
These triggers are useful when the same action is required after executing a complete operation.
Organizations often need to track who modified employee information. Triggers can automatically maintain an audit table.
CREATE TABLE Employee( id INT, name VARCHAR(50), salary INT );
CREATE TABLE Employee_Audit( employee_id INT, action_time DATETIME );
CREATE TRIGGER employee_insert_log AFTER INSERT ON Employee FOR EACH ROW BEGIN INSERT INTO Employee_Audit VALUES (NEW.id, NOW()); END;
Whenever a new employee is added, the database automatically records the activity.
In Part 1, we learned the basics of SQL Triggers, their types, syntax, and practical examples. In this section, we will explore advanced trigger concepts used in professional database applications.
Advanced triggers help database administrators and developers automate complex operations, maintain security, track changes, and enforce business rules without writing additional application code.
Database systems provide commands to create, view, and remove triggers. Proper trigger management is important for maintaining database performance and avoiding unnecessary complexity.
The CREATE TRIGGER command is used to create a new trigger in a database.
CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
BEGIN
SQL statements;
END;
CREATE TRIGGER order_insert_log AFTER INSERT ON Orders FOR EACH ROW BEGIN INSERT INTO Order_Log VALUES(NEW.order_id, NOW()); END;
This trigger automatically stores order creation details whenever a new order is inserted.
Database administrators often need to check available triggers in a database.
SHOW TRIGGERS;
This command displays all triggers available in the current database.
The DROP TRIGGER command is used when a trigger is no longer required.
DROP TRIGGER trigger_name;
DROP TRIGGER employee_log;
This permanently removes the specified trigger from the database.
Most database systems do not provide a direct ALTER TRIGGER command. Usually, developers remove the existing trigger and create a new one with updated logic.
DROP TRIGGER salary_update; CREATE TRIGGER salary_update AFTER UPDATE ON Employee FOR EACH ROW BEGIN INSERT INTO Salary_History VALUES(OLD.salary, NEW.salary); END;
| Feature | BEFORE Trigger | AFTER Trigger |
|---|---|---|
| Execution Time | Before database operation | After database operation |
| Main Purpose | Validation and modification | Logging and related actions |
| Common Use | Checking input values | Maintaining audit records |
One of the most common uses of triggers is creating an audit system. An audit table stores information about changes made in the database.
A company wants to track whenever employee salary information changes.
CREATE TABLE Salary_Audit ( Employee_ID INT, Old_Salary INT, New_Salary INT, Changed_Date DATETIME );
CREATE TRIGGER salary_change AFTER UPDATE ON Employee FOR EACH ROW BEGIN INSERT INTO Salary_Audit VALUES ( OLD.id, OLD.salary, NEW.salary, NOW() ); END;
Whenever salary is modified, previous and updated values are automatically stored.
Triggers can prevent invalid data from entering the database.
Suppose an organization does not allow employee age below 18.
CREATE TRIGGER check_age BEFORE INSERT ON Employee FOR EACH ROW BEGIN IF NEW.age < 18 THEN SET NEW.age = 18; END IF; END;
The trigger automatically corrects invalid age values before storing data.
Banking systems require strict monitoring of transactions. Triggers are useful for maintaining transaction history automatically.
Whenever money is withdrawn, the transaction details are stored in a history table.
CREATE TRIGGER transaction_history AFTER UPDATE ON Account FOR EACH ROW BEGIN INSERT INTO Transaction_Log VALUES ( NEW.account_id, NEW.balance, NOW() ); END;
This improves security and helps banks maintain accurate records.
Online shopping applications use triggers to automate inventory management.
When a product order is placed, product quantity can automatically decrease.
CREATE TRIGGER update_stock AFTER INSERT ON Orders FOR EACH ROW BEGIN UPDATE Product SET Quantity = Quantity - 1 WHERE Product_ID = NEW.Product_ID; END;
This keeps product availability updated automatically.
Educational systems can use triggers to maintain student activity records.
CREATE TRIGGER student_registration AFTER INSERT ON Student FOR EACH ROW BEGIN INSERT INTO Student_Log VALUES ( NEW.Student_ID, 'Registered', NOW() ); END;
Every new registration is automatically recorded.
Triggers and stored procedures both contain SQL logic, but they work differently.
| Feature | Trigger | Stored Procedure |
|---|---|---|
| Execution | Automatic | Manual call required |
| Parameter Support | No parameters | Supports parameters |
| Purpose | Automatic actions | Reusable operations |
| Control | Event based | User controlled |
| Feature | Triggers | Constraints |
|---|---|---|
| Purpose | Perform automatic actions | Maintain data rules |
| Complex Logic | Supports complex logic | Limited rules |
| Execution | Event based | Automatic validation |
Although triggers provide automation, poorly designed triggers can affect database performance.
A trigger is a database object that automatically executes when a specific event occurs.
It executes automatically during INSERT, UPDATE, or DELETE operations.
BEFORE, AFTER, INSERT, UPDATE, and DELETE triggers.
A trigger that executes before the database operation.
A trigger that executes after the database operation.
No, triggers cannot accept parameters.
They access previous and updated record values.
Triggers are stored inside the database schema.
Yes, depending on the database system configuration.
Yes, they can monitor and restrict database changes.
A trigger that records database activities.
Triggers execute automatically, while procedures require manual execution.
Yes, using DROP TRIGGER command.
Yes, syntax differs between database systems.
They may increase complexity and affect performance.
Yes, using validation logic.
INSERT, UPDATE, or DELETE operations.
To automate database tasks.
No, they run internally in the database.
To maintain good database performance.
SQL Triggers are powerful database automation tools that help maintain consistency, security, and accuracy. They are widely used in professional applications for auditing, validation, synchronization, and implementing business rules.
A good understanding of triggers helps database developers design efficient systems and write reliable database solutions. However, triggers should be used carefully because excessive or complex triggers can make database management difficult.
By learning trigger creation, management, optimization techniques, and practical applications, students can build strong SQL skills required for database development, interviews, and real-world projects.