What is a trigger in SQL? Explain its purpose, how it operates, and provide examples of scenarios where triggers are beneficial. Additionally, discuss the potential drawbacks of using triggers in a database environment.
The interviewer expects a clear explanation of triggers, including their types (BEFORE, AFTER, INSTEAD OF), how they are defined, and common use cases such as enforcing business rules, maintaining audit logs, and automating system tasks.
A trigger is a stored procedure that automatically executes in response to specific events on a particular table or view in a database.
CREATE TRIGGER update_timestamp BEFORE UPDATE ON users FOR EACH ROW SET NEW.updated_at = NOW();
Triggers can be categorized as BEFORE (executed before the triggering event), AFTER (executed after the event), or INSTEAD OF (replaces the event).
CREATE TRIGGER prevent_deletion BEFORE DELETE ON products FOR EACH ROW BEGIN IF OLD.stock > 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete products in stock'; END IF; END;
Common use cases for triggers include enforcing business rules (e.g., preventing invalid data), maintaining audit trails, and automatically updating related tables.
CREATE TRIGGER log_changes AFTER UPDATE ON orders FOR EACH ROW INSERT INTO order_audit (order_id, change_date) VALUES (NEW.id, NOW());
Triggers can complicate debugging and maintenance, lead to performance issues if not managed properly, and create hidden dependencies that may not be immediately apparent to developers.