SQL Database Triggers ⚙️
Master how to automate actions in your database using TRIGGERS for data validation, logging, and audit trails.
🚨 Introduction: Event-Driven Automation
A **Trigger** is a powerful mechanism that allows the database to automatically perform an action (a stored procedure) when a specific event occurs. Think of it as a **Security Guard** or an **Automated Rule** built into the table itself.
Triggers are primarily used to enforce complex business rules, audit data changes, or synchronize data across multiple tables. They run transparently to the user who executed the initial operation.
⚙️ Topic 1: CREATE TRIGGER Structure
Creating a trigger requires defining three main components: **Timing**, **Event**, and the **Action** to be performed.
📝 Trigger Components:
- Timing:
BEFOREorAFTERthe operation. - Event: The operation that fires the trigger (
INSERT,UPDATE, orDELETE). - Table: The specific table the event is watching.
- Action: The SQL code block to execute when the event happens.
💻 Example: Basic Trigger Structure
CREATE TRIGGER check_salaryBEFORE INSERT ON EmployeesFOR EACH ROWBEGIN -- Code logic goes hereEND;
⏱️ Topic 2: BEFORE vs AFTER Timing
The timing is the most important decision when creating a trigger, as it defines the trigger's primary purpose.
1. BEFORE Trigger (Validation):
Fires **before** the table is actually modified. Ideal for **validating data** (e.g., ensuring a negative value is never inserted) or modifying the data before insertion.
2. AFTER Trigger (Auditing/Logging):
Fires **after** the table is successfully modified. Ideal for **auditing** (logging the change) or updating related tables.
🛠️ Topic 3: Real-World Trigger Use Cases
Triggers are powerful for maintaining database health and complex business logic.
💻 Example: Auditing Changes (AFTER UPDATE)
-- Log every salary change into an Audit_Table AFTER the change happensCREATE TRIGGER log_salary_updateAFTER UPDATE ON EmployeesFOR EACH ROWBEGIN IF OLD.Salary <> NEW.Salary THEN INSERT INTO Audit_Table (EmpID, OldSalary, NewSalary) VALUES (OLD.EmployeeID, OLD.Salary, NEW.Salary); END IF;END;
📚 Module Summary
- Trigger: Event-driven stored procedure that runs automatically.
- Timing:
BEFORE(for validation) orAFTER(for logging/cascades). - Events: Activated by
INSERT,UPDATE, orDELETE. - Use Case: Enforce business rules and maintain audit trails.
🤔 Interview Q&A
Tap on the questions below to reveal the answers.