SQL Topics
BEFORE Trigger
title: BEFORE Trigger
In the previous lesson, you learned about Triggers and how they automatically execute when database events occur.
Triggers can execute:
Before An Event
After An Event
Instead Of An EventIn this lesson, we will focus on:
BEFORE TriggerA BEFORE Trigger executes before the actual database operation occurs.
This means the trigger gets an opportunity to:
Validate Data
Modify Data
Prevent Invalid Operations
Enforce Business Rulesbefore the INSERT, UPDATE, or DELETE operation is completed.
BEFORE Triggers are commonly used for validation and data integrity purposes.
What is a BEFORE Trigger?
A BEFORE Trigger is a trigger that executes before an INSERT, UPDATE, or DELETE operation is performed on a table.
Think of it as:
Security CheckpointBefore data enters the database:
Trigger Runs
↓
Checks Data
↓
Allows Or Rejects OperationSimple Definition
A BEFORE Trigger is a database trigger that executes automatically before a specified database event occurs.
Why Are BEFORE Triggers Important?
Suppose an employee's salary cannot be negative.
Without validation:
UPDATE Employees
SET Salary = -5000;could corrupt the database.
A BEFORE Trigger can prevent this.
Real-Life Analogy
Imagine airport security.
Process:
Passenger Arrives
↓
Security Check
↓
Allowed To BoardSimilarly:
Database Operation
↓
BEFORE Trigger
↓
Validation
↓
Operation ContinuesHow BEFORE Triggers Work
Workflow:
INSERT/UPDATE/DELETE Requested
↓
BEFORE Trigger Executes
↓
Validation Performed
↓
Operation Allowed Or BlockedTrigger Execution Flow
Example:
INSERT INTO Employees
VALUES (...);Database:
Events Supported by BEFORE Triggers
Most databases support BEFORE triggers for:
INSERT
UPDATE
DELETEBEFORE INSERT Trigger
Executes before:
INSERToperations.
Purpose:
Validate New Databefore insertion.
BEFORE UPDATE Trigger
Executes before:
UPDATEoperations.
Purpose:
Validate Modificationsbefore updating records.
BEFORE DELETE Trigger
Executes before:
DELETEoperations.
Purpose:
Prevent Unauthorized Deletionsor perform checks.
Basic BEFORE Trigger Syntax
MySQL Example:
CREATE TRIGGER TriggerName
BEFORE INSERT
ON TableName
FOR EACH ROW
BEGIN
SQL Statements;
END;Understanding the Syntax
CREATE TRIGGERCreates a trigger.
BEFORE INSERTDefines trigger timing and event.
ON TableNameSpecifies the table.
FOR EACH ROWExecutes for every affected row.
BEGIN
...
ENDContains trigger logic.
Creating Sample Table
CREATE TABLE Employees
(
EmployeeID INT,
EmployeeName VARCHAR(100),
Salary DECIMAL(10,2)
);BEFORE INSERT Example
Requirement:
Salary Must Be PositiveTrigger:
CREATE TRIGGER CheckSalary
BEFORE INSERT
ON Employees
FOR EACH ROW
BEGIN
IF NEW.Salary < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT =
'Salary Cannot Be Negative';
END IF;
END;How This Trigger Works
User tries:
INSERT INTO Employees
VALUES
(
1,
'Rahul',
-5000
);Trigger executes first.
Validation:
Salary < 0Result:
INSERT BlockedBEFORE UPDATE Example
Requirement:
Prevent Negative Salary UpdatesTrigger:
CREATE TRIGGER ValidateSalaryUpdate
BEFORE UPDATE
ON Employees
FOR EACH ROW
BEGIN
IF NEW.Salary < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT =
'Invalid Salary';
END IF;
END;Execution Example
User runs:
UPDATE Employees
SET Salary = -1000
WHERE EmployeeID = 1;Trigger blocks the update.
BEFORE DELETE Example
Requirement:
Prevent Deleting CEO RecordTrigger:
CREATE TRIGGER ProtectCEO
BEFORE DELETE
ON Employees
FOR EACH ROW
BEGIN
IF OLD.EmployeeID = 1 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT =
'CEO Cannot Be Deleted';
END IF;
END;Understanding OLD and NEW
BEFORE Triggers commonly use:
NEW
OLDkeywords.
NEW
Represents:
New Incoming DataExample:
NEW.SalaryNew salary value.
OLD
Represents:
Existing Database ValueExample:
OLD.SalaryCurrent salary before update.
Example Using NEW
IF NEW.Salary < 0 THENCheck incoming salary.
Example Using OLD
IF OLD.EmployeeID = 1 THENCheck existing employee record.
Real-World Example: Banking
Requirement:
Account Balance
Cannot Be NegativeTrigger validates balance before update.
Real-World Example: E-Commerce
Requirement:
Product Price
Cannot Be ZeroTrigger validates before insertion.
Real-World Example: Payroll
Requirement:
Salary Cannot Be Less Than Minimum WageTrigger validates salary before update.
Real-World Example: University System
Requirement:
Student Age Must Be >= 18Trigger validates before insert.
Advantages of BEFORE Triggers
Data Validation
Prevents invalid records.
Better Data Integrity
Ensures consistent data.
Automatic Enforcement
No manual validation needed.
Centralized Rules
Validation remains inside database.
Improved Security
Blocks unauthorized actions.
Disadvantages of BEFORE Triggers
Performance Overhead
Additional processing occurs.
Hidden Logic
Application developers may not know triggers exist.
Complex Debugging
Errors may originate from triggers.
Maintenance Challenges
Large trigger systems become difficult to manage.
Common Mistakes
Overloading Triggers
Too much logic inside triggers.
Infinite Trigger Chains
Triggers causing additional trigger executions.
Poor Error Messages
Difficult troubleshooting.
Ignoring Performance
Heavy validation slows transactions.
Best Practices
Keep Triggers Simple
Focus on one responsibility.
Use Meaningful Names
Example:
ValidateSalary
CheckBalance
ProtectCEODocument Trigger Logic
Improve maintainability.
Test Thoroughly
Validate all scenarios.
Monitor Performance
Evaluate trigger impact.
BEFORE Trigger vs AFTER Trigger
| Feature | BEFORE Trigger | AFTER Trigger |
|---|---|---|
| Executes Before Event | Yes | No |
| Executes After Event | No | Yes |
| Validation | Excellent | Limited |
| Prevent Operation | Yes | Usually No |
| Audit Logging | Possible | Better |
Common Interview Questions
What is a BEFORE Trigger?
A trigger that executes before a database event occurs.
Why are BEFORE Triggers used?
For validation and business rule enforcement.
Can a BEFORE Trigger stop an INSERT?
Yes.
It can reject invalid data.
What is NEW in a BEFORE Trigger?
Represents incoming row values.
What is OLD in a BEFORE Trigger?
Represents existing row values.
Summary
BEFORE Triggers execute before INSERT, UPDATE, or DELETE operations. They are commonly used for validation, enforcing business rules, preventing invalid data, and improving data integrity.
In this lesson, you learned:
- What BEFORE Triggers are
- How BEFORE Triggers work
- BEFORE INSERT
- BEFORE UPDATE
- BEFORE DELETE
- NEW and OLD keywords
- Real-world examples
- Advantages and disadvantages
- Best practices
Mastering BEFORE Triggers is important because they provide a powerful mechanism for validating and controlling data before it enters the database.
Next Step
Continue to the next lesson:
AFTER Trigger →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for BEFORE Trigger.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, triggers, before
Related SQL Topics