DBMS Topics
HubUnit 1 — Introduction to DBMS & Entity-Relationship ModelAdvantages and Disadvantages of DBMSAggregationCharacteristics of DBMSData IndependenceData ModelsDatabase Users and AdministratorsDBMS LanguagesER DiagramsEntity-Relationship ER ModelIntroduction to DBMSKeys in DBMSRelationships and CardinalitySchema and InstanceSpecialization and GeneralizationUnit 1 Summary — Introduction to DBMS & ER ModelThree-Level Architecture ANSI/SPARC ArchitectureWeak Entity SetUnit 2 — Relational Model & SQLConstraints in SQLCursorsDCL and TCL CommandsDDL CommandsDML CommandsDomain Relational Calculus DRCFunctions and Stored ProceduresJoins in SQLNested Queries SubqueriesRelational AlgebraRelational CalculusRelational ModelSQL IntroductionUnit 2 Summary — Relational Model & SQLTriggers in SQLTuple Relational Calculus TRCViews in SQLArmstrong's Axioms & Closure of AttributesBoyce-Codd Normal Form BCNFClosure of AttributesDependency PreservationFifth Normal Form 5NFFirst Normal Form 1NFFourth Normal Form 4NFFunctional DependencyLossless DecompositionMultivalued DependencyNormal Forms — OverviewNormalization — IntroductionSecond Normal Form 2NFUnit 3 Summary — NormalizationThird Normal Form 3NFUnit 4 — Transaction Management & Concurrency ControlACID PropertiesCheckpointsConcurrency Control & SerializabilityDeadlock in DBMSLock-Based Protocols & Two-Phase LockingLog-Based RecoveryRecovery SystemSerializabilityShadow PagingUnit 4 Summary — Transaction Management & Concurrency ControlTimestamp-Based ProtocolTransaction ManagementTransaction StatesTwo-Phase Locking 2PLUnit 5 — Storage & IndexingB+ TreeB-TreeCost EstimationDynamic HashingFile OrganizationHashing TechniquesIndexing — IntroductionMulti-Level IndexingQuery OptimizationQuery ProcessingSingle-Level IndexingStatic HashingUnit 5 Summary — Storage & IndexingUnit 6 — Advanced Topics in DBMSBig Data and DBMSCloud DatabaseData MiningData WarehouseDatabase SecurityDistributed DatabaseDistributed Query ProcessingDistributed DBMS Architecture, Fragmentation & ReplicationMongoDB — IntroductionNoSQL DatabaseReplication in Distributed DatabasesUnit 6 Summary — Advanced Topics in DBMSViva Voce — DBMSImportant Viva Questions — DBMSInterview Questions — DBMSViva Answers — DBMSDBMS Multiple Choice Questions MCQsDBMS Lab Programs
Triggers in SQL
Last Updated : 21 May, 2026
A trigger is a stored procedure that automatically executes fires in response to specific events on a table or view. Triggers are event-driven and cannot be called manual
Unit 2302 words13 headingsTables includedExamples included
What is a Trigger?
A trigger is a stored procedure that automatically executes (fires) in response to specific events on a table or view. Triggers are event-driven and cannot be called manually.
Event on Table
→Trigger fires automatically
→Actions execute
Trigger Components
Every trigger has these components:
| Component | Options | Description |
|---|---|---|
| Event | INSERT, UPDATE, DELETE | What DML event fires the trigger |
| Timing | BEFORE, AFTER | When the trigger fires |
| Level | FOR EACH ROW, FOR EACH STATEMENT | Row-level or statement-level |
| Table | Any base table | Which table is being monitored |
| Body | SQL / PL/SQL block | Actions to perform |
Trigger Syntax (MySQL)
sql exampleWoHoTech
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
-- trigger body
END;BEFORE vs. AFTER Triggers
| BEFORE Trigger | AFTER Trigger |
|---|---|
| Fires before the DML operation | Fires after the DML operation |
| Can modify NEW values (for INSERT/UPDATE) | Cannot change NEW values |
| Used for validation | Used for auditing, cascading |
| Can cancel the operation (via SIGNAL) | Operation already done |
OLD and NEW References
Inside a row-level trigger, you can access:
NEW.column— the new value being inserted/updatedOLD.column— the previous value before update/delete
| Operation | OLD | NEW |
|---|---|---|
| INSERT | Not available | New row values |
| UPDATE | Previous values | New values |
| DELETE | Previous values | Not available |
Example 1 — Audit Trigger (AFTER UPDATE)
Log every salary change to an audit table.
sql exampleWoHoTech
-- Create audit table
CREATE TABLE SalaryAudit (
audit_id INT AUTO_INCREMENT PRIMARY KEY,
emp_id INT,
old_salary DECIMAL(10, 2),
new_salary DECIMAL(10, 2),
changed_by VARCHAR(100) DEFAULT CURRENT_USER(),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create trigger
CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON Employee
FOR EACH ROW
BEGIN
IF OLD.salary <> NEW.salary THEN
INSERT INTO SalaryAudit (emp_id, old_salary, new_salary)
VALUES (OLD.emp_id, OLD.salary, NEW.salary);
END IF;
END;
-- Test
UPDATE Employee SET salary = 90000 WHERE emp_id = 101;
SELECT * FROM SalaryAudit;Example 2 — BEFORE INSERT Validation
Prevent inserting employees with salary below minimum.
sql exampleWoHoTech
CREATE TRIGGER trg_validate_salary
BEFORE INSERT ON Employee
FOR EACH ROW
BEGIN
IF NEW.salary < 15000 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary cannot be below minimum wage of 15000';
END IF;
END;
-- Test (will throw error)
INSERT INTO Employee VALUES (110, 'Test', 10000, 1, CURRENT_DATE);
-- Error: Salary cannot be below minimum wage of 15000Example 3 — BEFORE UPDATE (Auto-Capitalize Name)
sql exampleWoHoTech
CREATE TRIGGER trg_capitalize_name
BEFORE INSERT ON Employee
FOR EACH ROW
BEGIN
SET NEW.name = CONCAT(UPPER(LEFT(NEW.name, 1)), LOWER(SUBSTRING(NEW.name, 2)));
END;Example 4 — AFTER DELETE (Log Deletions)
sql exampleWoHoTech
CREATE TABLE DeletedEmployees (
emp_id INT,
name VARCHAR(100),
deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TRIGGER trg_log_delete
AFTER DELETE ON Employee
FOR EACH ROW
BEGIN
INSERT INTO DeletedEmployees (emp_id, name)
VALUES (OLD.emp_id, OLD.name);
END;Example 5 — Maintaining Derived Data
Automatically update a department's employee count.
sql exampleWoHoTech
-- Trigger for INSERT
CREATE TRIGGER trg_inc_emp_count
AFTER INSERT ON Employee
FOR EACH ROW
BEGIN
UPDATE Department SET emp_count = emp_count + 1
WHERE dept_id = NEW.dept_id;
END;
-- Trigger for DELETE
CREATE TRIGGER trg_dec_emp_count
AFTER DELETE ON Employee
FOR EACH ROW
BEGIN
UPDATE Department SET emp_count = emp_count - 1
WHERE dept_id = OLD.dept_id;
END;Managing Triggers
sql exampleWoHoTech
-- View all triggers
SHOW TRIGGERS;
SHOW TRIGGERS FROM university;
-- View trigger details
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = 'university';
-- Drop a trigger
DROP TRIGGER IF EXISTS trg_salary_audit;
-- Disable/Enable (MySQL 8+)
-- MySQL does not support DISABLE TRIGGER natively
-- Use conditional logic inside trigger body insteadAdvantages and Disadvantages of Triggers
| Advantages | Disadvantages |
|---|---|
| Automatic enforcement of business rules | Hard to debug and trace |
| Data integrity maintained automatically | Can slow down DML operations |
| Auditing and logging without app code | Trigger chains can be complex |
| Cascading actions across tables | Not visible in application code |
| Enforces complex constraints | Can cause unexpected side effects |
Trigger Flow
Application UPDATE
→BEFORE UPDATE trigger
→Validation
→Table update
→AFTER trigger / audit
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Triggers in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this DBMS topic.
Search Terms
dbms, database management system, database notes, sql, unit, triggers, triggers in sql
Related DBMS Topics
Continue learning this concept
Unit 2Constraints in SQLConstraints are rules applied to table columns that restrict the type of data that can be inserted or updated. They enforce data integrity at the database level — indepenUnit 2Joins in SQLA join combines rows from two or more tables based on a related column between them. Joins are the primary mechanism for querying data spread across multiple tables in a Unit 2Views in SQLA view is a virtual table defined by a stored SQL query. It does not physically store data — instead, it dynamically retrieves data from the underlying base tables whenevUnit 2Unit 2 — Relational Model & SQLThis unit covers the formal foundations of relational databases and the Structured Query Language SQL. Starting from the mathematical basis of relational algebra and calcUnit 2CursorsA cursor is a database object that allows row-by-row processing of query results inside a stored procedure or PL/SQL block. While SQL naturally operates on sets of rows, Unit 2DCL and TCL CommandsDCL commands control access and permissions to database objects. They manage security by defining who can do what.