Intermediate LevelSQL & MySQL
How do Database Triggers (BEFORE, AFTER, INSTEAD OF) execute automated logic?
❓ Interview Prompt: Explain Database Triggers, `BEFORE` vs `AFTER` vs `INSTEAD OF` timing, `OLD` and `NEW` pseudo-row references, and audit logging.
A Database Trigger is a procedural block of code executed automatically when a specified DML event ('INSERT', 'UPDATE', 'DELETE') occurs on a target table:
Trigger Timing & Pseudo-Rows:
•
'BEFORE' Trigger: Fires before row modification. Used to validate or transform incoming data ('NEW.col').
•
'AFTER' Trigger: Fires after row modification. Used for audit logging or updating secondary counters.
•
'OLD' vs 'NEW': 'OLD' references pre-modification row values ('UPDATE'/'DELETE'); 'NEW' references post-modification row values ('INSERT'/'UPDATE').
Code Implementation Example
sql
CREATE TRIGGER trg_users_audit_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
IF OLD.email <> NEW.email THEN
INSERT INTO audit_logs (user_id, old_value, new_value, changed_at)
VALUES (NEW.id, OLD.email, NEW.email, NOW());
END IF;
END;💡Creating an AFTER UPDATE trigger for automated audit logging.
🎯 Interview Key Takeaway
Use BEFORE triggers to validate or transform incoming row values, and AFTER triggers to generate audit logs.
#SQL#Triggers#BEFORE#AFTER#Audit Logging#Intermediate
Back to all SQL & MySQL Questions→