Triggers

Introduction

Database trigger: special database object. Executes automatically: in response to specific DML events (INSERT, UPDATE, DELETE). Event-driven: fired when condition met. Logic encapsulated: in database (not application). Use cases: validation, denormalization, audit trails, complex constraints.

"Triggers are the database's way of saying 'when this happens, do that automatically.' Powerful for enforcing rules, maintaining consistency, and automating complex operations,but use with caution, as they can hide logic and impact performance." -- Database programming

Trigger Definition

Concept

Named database object: contains SQL statements. Associated: with specific table. Triggered: automatically when event occurs. Execution: transparent to user (no explicit call). Timing: precise control (before, after, instead of).

Key Components

Event: INSERT, UPDATE, or DELETE. Timing: BEFORE, AFTER, or INSTEAD OF. Scope: row-level or statement-level. Action: executable SQL (procedural logic). Condition: optional (WHEN clause).

Comparison

Trigger Events

INSERT Event

Fired: when new row added. New values: available (NEW keyword). Old values: not applicable (NULL). Use: validation, cascading inserts, audit logging.

UPDATE Event

Fired: when row modified. Both values: available (OLD and NEW). Comparison: detect what changed. Use: historical tracking, computed columns, cascading updates.

DELETE Event

Fired: before or after row removed. Old values: available (OLD keyword). New values: not applicable (NULL). Use: prevent deletion, clean up related data, audit trail.

Event Combinations

CREATE TRIGGER audit_employee_changesAFTER INSERT OR UPDATE OR DELETE ON employees-- Triggered by any of three operations

Timing: BEFORE vs AFTER

BEFORE Triggers

Execution: before DML operation completes. Access: NEW values (can modify). Validation: enforce rules before insert/update. Modification: alter incoming data. Rejection: can prevent operation (return false or raise error).

AFTER Triggers

Execution: after DML operation succeeds. Access: NEW (updated values) and OLD (original values). Verification: check results. Side effects: cascade operations, update summary tables. No prevention: operation already committed.

INSTEAD OF Triggers

Execution: replaces original operation. Useful: on views (updatable views). Logic: custom implementation. Control: complete flexibility. Example: instead of UPDATE view, update underlying tables.

Example

-- BEFORE INSERT: validate and modifyCREATE TRIGGER check_salary BEFORE INSERT ON employeesFOR EACH ROWBEGIN IF NEW.salary < 0 THEN SET NEW.salary = 0; END IF;END;-- AFTER INSERT: cascade actionCREATE TRIGGER update_dept_count AFTER INSERT ON employeesFOR EACH ROWBEGIN UPDATE departments SET emp_count = emp_count + 1 WHERE dept_id = NEW.dept_id;END;

Row-Level vs Statement-Level

Row-Level Triggers

Execution: once per affected row. Frequency: multiple invocations (one per row). Context: individual row data (NEW, OLD available). Use: logic dependent on individual row values.

Statement-Level Triggers

Execution: once per statement (regardless of rows affected). Frequency: single invocation. Context: operation-level (no row-specific data). Use: summary updates, overall validation.

Performance Implications

Row-level: overhead scales with rows (bulk operations expensive). Statement-level: fixed cost (better for bulk). Design: choose appropriately. Bulk operations: prefer statement-level when possible.

Example

-- Row-level: fires once per rowCREATE TRIGGER calc_total_row AFTER INSERT ON order_itemsFOR EACH ROWBEGIN UPDATE orders SET total = total + (NEW.quantity * NEW.price) WHERE order_id = NEW.order_id;END;-- Statement-level: fires once per statementCREATE TRIGGER calc_total_stmt AFTER INSERT ON order_itemsFOR EACH STATEMENTBEGIN UPDATE orders SET total = (SELECT SUM(quantity * price) FROM order_items WHERE order_id = orders.order_id);END;

Trigger Syntax

Basic Syntax

CREATE TRIGGER trigger_name{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}ON table_name[FOR EACH ROW]BEGIN -- Trigger body (SQL statements)END;

With Condition

CREATE TRIGGER validate_price BEFORE INSERT ON productsFOR EACH ROWWHEN (NEW.price > 0)BEGIN -- Only execute if price > 0END;

Modification

ALTER TRIGGER trigger_name DISABLE;ALTER TRIGGER trigger_name ENABLE;DROP TRIGGER trigger_name;

Multiple Triggers

Same table: multiple triggers possible (same or different events). Execution order: defined by DBMS (predictability varies). Caution: complex interactions.

Trigger Actions

DML Operations

INSERT, UPDATE, DELETE: perform data modifications. Within trigger: executed in same transaction. Cascading: can trigger other triggers (recursive).

Control Flow

IF condition THEN -- statementsELSE -- alternativeEND IF;CASE value WHEN condition1 THEN -- statements WHEN condition2 THEN -- statements ELSE -- defaultEND CASE;

Error Handling

BEGIN TRY -- statementsEND TRYBEGIN CATCH RAISE EXCEPTION 'Error message';END CATCH;

Special Keywords

NEW: new row values (INSERT, UPDATE). OLD: old row values (UPDATE, DELETE). For BEFORE triggers: NEW values can be modified. For AFTER triggers: values are read-only.

Common Use Cases

Audit Trail

CREATE TRIGGER audit_employee_updateAFTER UPDATE ON employeesFOR EACH ROWBEGIN INSERT INTO employee_audit (emp_id, old_salary, new_salary, changed_date) VALUES (OLD.emp_id, OLD.salary, NEW.salary, NOW());END;

Enforce Business Rules

CREATE TRIGGER check_hire_date BEFORE INSERT ON employeesFOR EACH ROWBEGIN IF NEW.hire_date > CURDATE() THEN RAISE EXCEPTION 'Hire date cannot be in the future'; END IF;END;

Maintain Denormalized Data

CREATE TRIGGER update_dept_salary AFTER INSERT ON employeesFOR EACH ROWBEGIN UPDATE departments SET total_salary = total_salary + NEW.salary WHERE dept_id = NEW.dept_id;END;

Prevent Orphan Records

CREATE TRIGGER prevent_orphan_delete BEFORE DELETE ON departmentsFOR EACH ROWBEGIN IF EXISTS (SELECT 1 FROM employees WHERE dept_id = OLD.dept_id) THEN RAISE EXCEPTION 'Cannot delete department with employees'; END IF;END;

Generate Computed Columns

CREATE TRIGGER calc_full_name BEFORE INSERT ON employeesFOR EACH ROWBEGIN SET NEW.full_name = CONCAT(NEW.first_name, ' ', NEW.last_name);END;

Performance Implications

Overhead

Compilation: triggers compiled at creation. Execution: added latency per DML. Bulk operations: significant impact (multiple triggers per row). Cascading: can create chain reactions.

Optimization

Minimize logic: keep triggers simple. Avoid cascading: indirect operations. Statement-level: prefer over row-level for bulk. Conditional triggers: use WHEN clause to skip unnecessary execution.

Monitoring

-- Monitor trigger executionSELECT * FROM information_schema.triggers WHERE trigger_schema = 'mydb';-- Disable triggers for bulk operationsALTER TRIGGER tr_name DISABLE;-- Perform bulk operationsALTER TRIGGER tr_name ENABLE;

Example Impact

Scenario: insert 10,000 rows with row-level trigger. Cost: trigger executes 10,000 times (expensive). Alternative: statement-level trigger (single execution). Difference: 100x+ performance improvement possible.

Debugging and Monitoring

Common Issues

Silent failures: triggers fail without notification. Cascading problems: one trigger causes another to fail. Recursion: infinite loops (A triggers B, B triggers A). Data inconsistency: partial updates.

Debug Techniques

-- Audit table for debuggingCREATE TABLE trigger_log ( id INT PRIMARY KEY AUTO_INCREMENT, trigger_name VARCHAR(100), event_type VARCHAR(50), row_id INT, executed_at TIMESTAMP);-- Inside triggerINSERT INTO trigger_log (trigger_name, event_type, row_id, executed_at)VALUES ('tr_name', 'INSERT', NEW.id, NOW());

Prevent Infinite Loops

-- Use flag to prevent recursionSET @trigger_disabled = 0;CREATE TRIGGER avoid_recursion BEFORE UPDATE ON table_nameFOR EACH ROWBEGIN IF @trigger_disabled = 0 THEN SET @trigger_disabled = 1; -- Trigger logic SET @trigger_disabled = 0; END IF;END;

Test Coverage

Unit test: test each trigger independently. Integration test: test with other triggers. Edge cases: NULL values, bulk operations, constraint violations.

Best Practices

Design Principles

Minimize logic: keep triggers simple and focused. Document thoroughly: explain why trigger exists. Avoid hidden logic: make trigger behavior visible to developers. Version control: track trigger changes.

Performance

Avoid cascading: limit trigger chains. Batch operations: statement-level when possible. Monitor impact: profile before/after. Conditional execution: use WHEN clause.

Maintainability

Naming convention: clear purpose (e.g., audit_, update_, validate_). One responsibility: trigger does one thing. Error handling: explicit (log errors, notify).

Testing

Test all event types: INSERT, UPDATE, DELETE. Test edge cases: NULL values, boundary conditions. Test side effects: ensure cascading works. Test performance: bulk operations.

Limitations and Cautions

Visibility and Debugging

Hidden logic: triggers execute invisibly (developers may not realize). Stack traces: harder to debug. Performance profiling: must account for trigger overhead. Documentation: often lacking.

Portability

Syntax varies: by DBMS (MySQL, SQL Server, PostgreSQL different). Features differ: recursion handling, NEW/OLD availability. Migration: triggers don't transfer easily.

Recursion and Cascading

Infinite loops: possible (A triggers B, B triggers A). Cascading failures: one trigger breaks another. Debugging complex: hard to trace root cause.

Alternative Approaches

Application logic: often better (visible, maintainable, testable). Stored procedures: explicit execution (clearer intent). Constraints: declarative (simpler, safer). Events: scheduled jobs (batch processing).

When NOT to Use

Complex business logic: use application code instead. Performance-critical: avoid trigger overhead. Portability needed: avoid DBMS-specific syntax. Team unfamiliar: document extensively.

References

• Ramakrishnan, R., and Gehrke, J. "Database Management Systems." McGraw-Hill, 3rd edition, 2003.
• Silberschatz, A., Korth, H. F., and Sudarshan, S. "Database System Concepts." McGraw-Hill, 6th edition, 2010.
• Date, C. J. "An Introduction to Database Systems." Pearson, 8th edition, 2003.
• Garcia-Molina, H., Ullman, J. D., and Widom, J. "Database Systems: The Complete Book." Pearson, 2nd edition, 2008.
• Korth, H. F., Silberschatz, A., and Sudarshan, S. "Database System Concepts." McGraw-Hill, 6th edition, 2010.