Triggers in SQL: BEFORE vs AFTER
💡 Concept Name
SQL Triggers are special procedures that automatically execute in response to specific table events like INSERT, UPDATE, or DELETE.
📘 Quick Intro
Triggers can be defined to run BEFORE or AFTER a data manipulation operation. They automate tasks like validation, logging, and enforcing business rules without modifying client applications.
🧠 Analogy / Short Story
Imagine a hotel receptionist (trigger) checking guest IDs. A BEFORE trigger checks ID **before** giving the key. An AFTER trigger records the guest info in logs **after** check-in is complete. Similarly, SQL triggers act before or after a database change.
🔧 Technical Explanation
- ⏰ BEFORE Trigger: Executes before the triggering operation is applied. Used for validation or modifying data.
- ✅ AFTER Trigger: Executes after the operation completes. Ideal for logging or cascading effects.
- 🔄 Common trigger events: INSERT, UPDATE, DELETE.
- ⚙️ Triggers are bound to a specific table or view.
- 🔒 Can enforce complex integrity constraints or maintain audit trails.
🎯 Purpose & Use Case
- ✅ Automatically update audit columns (e.g., last_modified_date).
- ✅ Validate data before it's inserted or updated.
- ✅ Log actions to an audit table after data changes.
- ✅ Maintain consistency across related tables.
💻 Real Code Example
-- BEFORE trigger to validate email
CREATE TRIGGER check_email_before_insert
BEFORE INSERT ON Users
FOR EACH ROW
BEGIN
IF NEW.Email NOT LIKE '%@%' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Invalid email format';
END IF;
END;
-- AFTER trigger to log insert
CREATE TRIGGER log_user_after_insert
AFTER INSERT ON Users
FOR EACH ROW
BEGIN
INSERT INTO UserLog (UserId, Action)
VALUES (NEW.Id, 'INSERT');
END;
❓ Interview Q&A
Q1: What is a trigger in SQL?
A: A trigger is a special stored procedure that runs automatically in response to data-modifying events like INSERT, UPDATE, or DELETE.
Q2: What’s the difference between BEFORE and AFTER triggers?
A: BEFORE triggers run before the DML action, allowing validation or changes. AFTER triggers run after the action, good for logging or notifications.
Q3: Can triggers modify data before it is committed?
A: Yes, BEFORE triggers can modify or reject the data before it’s saved.
Q4: Can you call another trigger from inside a trigger?
A: Not directly, but cascading actions may cause other triggers to run.
Q5: What are common use cases for AFTER triggers?
A: Logging actions, sending notifications, syncing audit tables.
Q6: Can a trigger be defined on a view?
A: In some DBMSs like PostgreSQL, yes, but with limitations. Generally, triggers are table-based.
Q7: What does NEW and OLD refer to inside a trigger?
A: NEW refers to the new row values being inserted/updated; OLD refers to previous row values during update/delete.
Q8: Can triggers slow down performance?
A: Yes, if poorly written or overly complex—they add overhead to DML operations.
Q9: Can you disable triggers temporarily?
A: Yes, many DBMSs allow disabling/enabling triggers for maintenance.
Q10: What is a recursive trigger?
A: A trigger that causes itself to fire again—most DBMSs allow you to prevent recursion with options.
📝 MCQs
Q1. When does a BEFORE trigger execute?
- After the commit
- Before the data change occurs
- After rollback
- Before constraint checks
Q2. What is a typical use case for an AFTER trigger?
- Modifying input data
- Constraint checking
- Logging user activity
- Blocking inserts
Q3. Which keyword is used to access new values inside a trigger?
- OLD
- PREV
- NEW
- RECENT
Q4. What does a BEFORE trigger allow that AFTER doesn’t?
- Query logging
- Rollback data
- Data validation before commit
- Email notification
Q5. Triggers can be created on which of the following?
- Views only
- Tables
- Databases
- Schemas
Q6. Which event type is NOT valid for a trigger?
- INSERT
- UPDATE
- DELETE
- SELECT
Q7. What happens if a BEFORE trigger fails?
- It is ignored
- The operation is aborted
- The trigger is skipped
- AFTER trigger executes
Q8. How many triggers can be created on a table?
- Only 1
- Only 2
- Only 3
- Multiple
Q9. What does OLD refer to in an UPDATE trigger?
- New values
- Table name
- Trigger name
- Previous values of the row
Q10. Which statement is true about AFTER triggers?
- They block inserts
- They replace stored procedures
- They run before the data is changed
- They run after the DML action completes
💡 Bonus Insight
Combine triggers with audit tables for automatic change tracking. Also, avoid complex logic in triggers—they are powerful but can silently introduce performance bottlenecks or recursive issues if not monitored.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!