DBMS Notes
HubUnit 1 — Introduction to DBMS & Entity-Relationship ModelAdvantages and Disadvantages of DBMSAggregation — Database Management SystemsCharacteristics of DBMSData IndependenceData ModelsDatabase Users and AdministratorsDBMS LanguagesER DiagramsEntity-Relationship (ER) ModelIntroduction to DBMSKeys in DBMSRelationships and CardinalitySchema and InstanceSpecialization and GeneralizationDBMS Unit 1 Summary - Database FundamentalsThree-Level Architecture (ANSI/SPARC Architecture)Weak Entity SetUnit 2 — Relational Model & SQLConstraints in SQLCursorsDCL and TCL CommandsDDL CommandsDML CommandsDomain Relational Calculus (DRC) - Complete TutorialFunctions and Stored ProceduresJoins in SQLNested Queries (Subqueries)Relational AlgebraRelational Calculus in Database Management SystemsRelational ModelSQL IntroductionDBMS Unit 2 Summary - SQL and Relational ModelTriggers in SQL — Database Management SystemsTuple Relational Calculus (TRC)Views in SQL — Database Management SystemsArmstrong\Boyce-Codd Normal Form (BCNF) — Database Management SystemsClosure of AttributesDependency PreservationFifth Normal Form (5NF)First Normal Form (1NF) - Database NormalizationFourth Normal Form (4NF)Functional DependencyLossless DecompositionMultivalued DependencyNormal Forms — OverviewNormalization — IntroductionSecond Normal Form (2NF) — Database Management SystemsUnit 3 Summary — NormalizationThird Normal Form (3NF) — Database Management SystemsUnit 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 (2PL)Unit 5 — Storage & IndexingB+ TreeB-TreeCost EstimationDynamic HashingFile OrganizationHashing TechniquesIndexing — IntroductionMulti-Level IndexingQuery Optimization — Database Management SystemsQuery ProcessingSingle-Level IndexingStatic HashingUnit 5 Summary — Storage & IndexingUnit 6 — Advanced Topics in 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 (MCQs)DBMS Lab Programs
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...
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 Syntax (MySQL)
sql example
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 example
-- 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 example
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 example
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 example
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 example
-- 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 example
-- 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
Continue the lesson path
Study next in this module
01Unit 2DBMS Unit 2 Summary - SQL and Relational ModelComprehensive guide to SQL commands (DDL, DML, DCL, TCL), relational model, data relationships, query processing, and ACID transactions. Master SQL fundamentals with detailed examples and interview preparation.02Unit 2Tuple Relational Calculus (TRC)Tuple Relational Calculus (TRC) is a non-procedural query language where each query is expressed as:03Unit 2SQL IntroductionSQL (Structured Query Language) is the standard language for interacting with relational databases. It allows users to define, manipulate, and control data...04Unit 2Views in SQL — Database Management SystemsA 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...