SQL Topics
HubAdvantages and Disadvantages of SQLFeatures of SQLHistory of SQLSQL vs NoSQLWhat is SQL?SQL CommentsSQL Data TypesSQL KeywordsSQL OperatorsSQL SyntaxCHECKComposite KeysDEFAULTFOREIGN KEYNOT NULLPRIMARY KEYSQL ConstraintsUNIQUEDELETE DataINSERT DataSELECT DataSQL AliasesUPDATE DataBETWEEN OperatorDISTINCT KeywordGROUP BY ClauseHAVING ClauseIN OperatorLIKE OperatorLIMIT ClauseORDER BY ClauseTOP ClauseWHERE ClauseWildcard CharactersEXCEPT Exclusion Rows OperatorINTERSECT Mutual Rows OperatorMINUS Difference Operator DatabaseUNION Deduplicated Set OperatorUNION ALL Combined Stack OperatorCROSS JOINFULL OUTER JOININNER JOINJOIN vs UNIONLEFT JOINRIGHT JOINSELF JOINSQL Joins OverviewANY and ALL OperatorsCASE ExpressionCorrelated SubqueryEXISTS OperatorNested SubqueriesScalar SubqueriesSubquery IntroductionAggregate FunctionsAVG FunctionConversion FunctionsCOUNT FunctionDate FunctionsMIN and MAX FunctionsNumeric FunctionsString FunctionsSUM FunctionALTER TABLECreate TableDROP TABLERename TableTemporary TableTRUNCATE TABLEBackup DatabaseCreate DatabaseDrop DatabaseRestore DatabaseCREATE VIEW Saved SELECT StatementsDROP VIEW Discard virtual tablesMaterialized Views Cash & StorageUPDATE VIEW ProceduresCreate ProcedureExecute ProcedureParameters in ProcedureProcedure vs FunctionStored Procedure IntroductionAFTER TriggerBEFORE TriggerINSTEAD OF TriggerTrigger ExamplesTrigger IntroductionACID Properties in SQLCOMMIT in SQLIsolation Levels in SQLROLLBACK in SQLSAVEPOINT in SQLRelational Transactions Control BasicsBoyce-Codd Normal Form BCNFDatabase NormalizationDenormalizationFirst Normal Form 1NFSecond Normal Form 2NFThird Normal Form 3NFClustered Indexes Storage Physical LayoutIndex PerformanceNon-Clustered IndexSQL Indexing Strategies IntroductionUnique IndexCommon Table Expressions CTEDynamic SQLFunctions in SQLMaterialized ViewsMERGE StatementPIVOT and UNPIVOTRANK, DENSERANK, and ROWNUMBERRecursive CTEStored ProceduresTriggers in SQLViews in SQLWindow FunctionsSQL Interview Questions AdvancedSQL Interview Questions BeginnerSQL Interview Questions IntermediateSQL Output-Based Interview QuestionsSQL Scenario-Based Interview QuestionsAnalyzing Execution Plan DiagramsEXPLAIN PlanIndexing StrategiesPartitioningQuery OptimizationSQL Performance TipsSQL ChallengesSQL Exercises AdvancedSQL Exercises BeginnerSQL Exercises IntermediateSQL ProjectsCreating Your First Database ConnectionInstall MySQLInstall PostgreSQLInstall SQLiteSQL Online CompilersSQL Roadmap
Trigger Examples
title: Trigger Examples
In the previous lessons, you learned about:
text exampleWoHoTech
Trigger Introduction
BEFORE Trigger
AFTER Trigger
INSTEAD OF TriggerUnderstanding trigger syntax is important, but real learning happens when you see practical examples.
In enterprise applications, triggers are commonly used for:
text exampleWoHoTech
Audit Logging
Data Validation
Inventory Management
Salary Tracking
Security Controls
Automatic CalculationsThis lesson contains multiple real-world trigger examples that demonstrate how triggers are used in production database systems.
Why Study Trigger Examples?
Many developers understand trigger syntax but struggle to apply triggers effectively.
Practical examples help you understand:
text exampleWoHoTech
When To Use Triggers
How To Design Triggers
How To Avoid Common Mistakes
How Enterprise Systems Use TriggersExample 1: Audit Logging Trigger
One of the most common trigger use cases.
Requirement:
text exampleWoHoTech
Whenever Employee Data Changes
Store Audit InformationCreate Employee Table
sql exampleWoHoTech
CREATE TABLE Employees
(
EmployeeID INT,
EmployeeName VARCHAR(100),
Salary DECIMAL(10,2)
);Create Audit Table
sql exampleWoHoTech
CREATE TABLE EmployeeAudit
(
AuditID INT AUTO_INCREMENT,
EmployeeID INT,
ActionType VARCHAR(20),
ActionDate DATETIME
);AFTER UPDATE Trigger
sql exampleWoHoTech
CREATE TRIGGER EmployeeUpdateAudit
AFTER UPDATE
ON Employees
FOR EACH ROW
BEGIN
INSERT INTO EmployeeAudit
(
EmployeeID,
ActionType,
ActionDate
)
VALUES
(
NEW.EmployeeID,
'UPDATE',
NOW()
);
END;Result
Whenever:
sql exampleWoHoTech
UPDATE Employees
SET Salary = 60000
WHERE EmployeeID = 1;executes:
text exampleWoHoTech
Audit Record Created AutomaticallyExample 2: Salary Change History
Requirement:
text exampleWoHoTech
Track Old Salary
Track New Salary
Store Change DateCreate History Table
sql exampleWoHoTech
CREATE TABLE SalaryHistory
(
EmployeeID INT,
OldSalary DECIMAL(10,2),
NewSalary DECIMAL(10,2),
ChangeDate DATETIME
);AFTER UPDATE Trigger
sql exampleWoHoTech
CREATE TRIGGER SalaryTracker
AFTER UPDATE
ON Employees
FOR EACH ROW
BEGIN
INSERT INTO SalaryHistory
VALUES
(
NEW.EmployeeID,
OLD.Salary,
NEW.Salary,
NOW()
);
END;Result
Old and new salary values are preserved automatically.
Example 3: Prevent Negative Salary
Requirement:
text exampleWoHoTech
Salary Cannot Be NegativeBEFORE INSERT Trigger
sql exampleWoHoTech
CREATE TRIGGER ValidateSalary
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;Result
Invalid salary insertions are blocked.
Example 4: Prevent Salary Reduction
Requirement:
text exampleWoHoTech
Employee Salary
Cannot DecreaseBEFORE UPDATE Trigger
sql exampleWoHoTech
CREATE TRIGGER PreventSalaryReduction
BEFORE UPDATE
ON Employees
FOR EACH ROW
BEGIN
IF NEW.Salary < OLD.Salary THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT =
'Salary Reduction Not Allowed';
END IF;
END;Result
Salary reductions are rejected.
Example 5: Inventory Management
Requirement:
text exampleWoHoTech
Reduce Inventory
When Order CreatedProducts Table
sql exampleWoHoTech
CREATE TABLE Products
(
ProductID INT,
ProductName VARCHAR(100),
Quantity INT
);Orders Table
sql exampleWoHoTech
CREATE TABLE Orders
(
OrderID INT,
ProductID INT,
Quantity INT
);AFTER INSERT Trigger
sql exampleWoHoTech
CREATE TRIGGER UpdateInventory
AFTER INSERT
ON Orders
FOR EACH ROW
BEGIN
UPDATE Products
SET Quantity =
Quantity - NEW.Quantity
WHERE ProductID =
NEW.ProductID;
END;Result
Inventory updates automatically.
Example 6: Prevent Product Deletion
Requirement:
text exampleWoHoTech
Products With Orders
Cannot Be DeletedBEFORE DELETE Trigger
sql exampleWoHoTech
CREATE TRIGGER PreventProductDelete
BEFORE DELETE
ON Products
FOR EACH ROW
BEGIN
IF EXISTS
(
SELECT *
FROM Orders
WHERE ProductID =
OLD.ProductID
)
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT =
'Product Has Orders';
END IF;
END;Result
Deletion prevented.
Example 7: Banking Transaction Log
Requirement:
text exampleWoHoTech
Track Every Balance ChangeAccounts Table
sql exampleWoHoTech
CREATE TABLE Accounts
(
AccountID INT,
Balance DECIMAL(10,2)
);Transaction Log Table
sql exampleWoHoTech
CREATE TABLE TransactionLog
(
AccountID INT,
OldBalance DECIMAL(10,2),
NewBalance DECIMAL(10,2),
TransactionDate DATETIME
);AFTER UPDATE Trigger
sql exampleWoHoTech
CREATE TRIGGER BalanceAudit
AFTER UPDATE
ON Accounts
FOR EACH ROW
BEGIN
INSERT INTO TransactionLog
VALUES
(
NEW.AccountID,
OLD.Balance,
NEW.Balance,
NOW()
);
END;Result
Every balance change is logged.
Example 8: Automatic Timestamp Update
Requirement:
text exampleWoHoTech
Update Modified Date
AutomaticallyEmployees Table
sql exampleWoHoTech
CREATE TABLE Employees
(
EmployeeID INT,
EmployeeName VARCHAR(100),
ModifiedDate DATETIME
);BEFORE UPDATE Trigger
sql exampleWoHoTech
CREATE TRIGGER UpdateTimestamp
BEFORE UPDATE
ON Employees
FOR EACH ROW
BEGIN
SET NEW.ModifiedDate =
NOW();
END;Result
Modification date updates automatically.
Example 9: Soft Delete Implementation
Requirement:
text exampleWoHoTech
Do Not Delete Records
Mark Them As DeletedTable
sql exampleWoHoTech
CREATE TABLE Employees
(
EmployeeID INT,
EmployeeName VARCHAR(100),
IsDeleted BOOLEAN
);INSTEAD OF DELETE Trigger
(SQL Server)
sql exampleWoHoTech
CREATE TRIGGER SoftDelete
ON Employees
INSTEAD OF DELETE
AS
BEGIN
UPDATE Employees
SET IsDeleted = 1
WHERE EmployeeID IN
(
SELECT EmployeeID
FROM DELETED
);
END;Result
Records remain in database.
Only marked as deleted.
Example 10: Student Age Validation
Requirement:
text exampleWoHoTech
Student Must Be At Least 18
Years OldBEFORE INSERT Trigger
sql exampleWoHoTech
CREATE TRIGGER ValidateAge
BEFORE INSERT
ON Students
FOR EACH ROW
BEGIN
IF NEW.Age < 18 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT =
'Minimum Age Is 18';
END IF;
END;Result
Underage students cannot be inserted.
Example 11: Order Audit System
Requirement:
text exampleWoHoTech
Track Every New OrderAFTER INSERT Trigger
sql exampleWoHoTech
CREATE TRIGGER OrderAudit
AFTER INSERT
ON Orders
FOR EACH ROW
BEGIN
INSERT INTO OrderHistory
VALUES
(
NEW.OrderID,
NOW()
);
END;Result
Order history maintained automatically.
Example 12: Restrict Weekend Transactions
Requirement:
text exampleWoHoTech
No Transactions On SundayBEFORE INSERT Trigger
sql exampleWoHoTech
CREATE TRIGGER WeekendRestriction
BEFORE INSERT
ON Transactions
FOR EACH ROW
BEGIN
IF DAYOFWEEK(NOW()) = 1 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT =
'Transactions Disabled';
END IF;
END;Result
Sunday transactions blocked.
Real-World Trigger Use Cases
Banking
text exampleWoHoTech
Balance Tracking
Transaction Logs
Fraud MonitoringE-Commerce
text exampleWoHoTech
Inventory Updates
Order Audits
Sales AnalyticsHospital Systems
text exampleWoHoTech
Patient Record Audits
Prescription Tracking
Appointment LogsPayroll Systems
text exampleWoHoTech
Salary History
Tax Audits
Bonus TrackingUniversity Systems
text exampleWoHoTech
Student Registration Logs
Attendance Tracking
Course Enrollment AuditsCommon Trigger Design Patterns
Audit Pattern
text exampleWoHoTech
Track ChangesValidation Pattern
text exampleWoHoTech
Block Invalid DataAutomation Pattern
text exampleWoHoTech
Automatic UpdatesSecurity Pattern
text exampleWoHoTech
Prevent Unauthorized ActionsSynchronization Pattern
text exampleWoHoTech
Keep Tables ConsistentBest Practices
Keep Triggers Small
One responsibility per trigger.
Avoid Heavy Logic
Complex processing hurts performance.
Document Trigger Purpose
Improve maintainability.
Test Thoroughly
Validate all scenarios.
Monitor Performance
Check impact on transactions.
Common Interview Questions
What are the most common trigger use cases?
Audit logging, validation, automation, and security.
Can triggers update other tables?
Yes.
Triggers can perform operations on multiple tables.
Are triggers useful for auditing?
Absolutely.
Audit logging is one of the most common trigger applications.
Can triggers prevent invalid data?
Yes.
BEFORE triggers are frequently used for validation.
Which trigger type is best for audit logging?
AFTER Trigger.
Summary
Triggers are powerful automation tools used throughout enterprise database systems. They help enforce business rules, maintain audit trails, automate updates, and ensure data integrity.
In this lesson, you learned:
- Audit Logging Triggers
- Salary Tracking Triggers
- Validation Triggers
- Inventory Management Triggers
- Banking Triggers
- Soft Delete Triggers
- Timestamp Triggers
- Security Triggers
- Real-world use cases
- Best practices
Mastering trigger examples is important because real-world database development relies heavily on practical trigger implementations rather than theoretical concepts alone.
Next Step
Continue to the next module:
Common Table Expressions (CTE) →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Trigger Examples.
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, trigger
Related SQL Topics
Continue learning this concept
TriggersAFTER Triggertitle: AFTER TriggerTriggersBEFORE Triggertitle: BEFORE TriggerTriggersINSTEAD OF Triggertitle: INSTEAD OF TriggerTriggersTrigger Introductiontitle: Trigger IntroductionAdvanced SQLTriggers in SQLtitle: Triggers in SQLAdvanced SQLCommon Table Expressions CTEtitle: Common Table Expressions CTE