Here’s a clear, detailed comparison between Triggers and Stored Procedures in MySQL:
| Feature | Trigger | Stored Procedure |
|---|---|---|
| Definition | A database object that automatically executes in response to certain events (INSERT, UPDATE, DELETE) on a table. | A database object that contains a set of SQL statements and is executed explicitly by the user or application. |
| Execution | Automatic – executes when the triggering event occurs. | Manual/Explicit – must be called using CALL procedure_name(). |
| Events | Triggered by DML events (INSERT, UPDATE, DELETE) on a table. | Can be executed anytime and can perform any SQL operations, including calling other procedures. |
| Control | Cannot be directly invoked; only runs in response to events. | Can be invoked anytime using CALL, giving more control. |
| Parameters | Cannot accept parameters directly. | Can accept input, output, and input-output parameters. |
| Use Case | Enforce business rules automatically, maintain audit logs, or replicate changes. | Encapsulate repetitive logic, perform batch operations, or modularize code. |
| Transaction Scope | Part of the transaction that fired it; failure can roll back the main transaction (depending on DBMS). | Can start its own transaction or be part of a transaction; more flexible. |
| Return Value | Cannot return a value to the caller. | Can return values via output parameters or result sets. |
Example:
- Trigger: Automatically logs every deletion from
employeestable:
CREATE TRIGGER log_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
INSERT INTO employee_audit(emp_id, action_date, action_type)
VALUES (OLD.id, NOW(), 'DELETE');
- Stored Procedure: Deletes an employee by ID and logs it:
CREATE PROCEDURE delete_employee(IN empID INT)
BEGIN
DELETE FROM employees WHERE id = empID;
INSERT INTO employee_audit(emp_id, action_date, action_type)
VALUES (empID, NOW(), 'DELETE');
END;
✅ Key takeaway:
- Trigger = automatic response to table events
- Stored Procedure = manual execution of encapsulated logic