MySQL Triggers and Events Automation
MySQL triggers and events are powerful automation tools that execute code automatically in response to data changes or on scheduled intervals. Triggers enforce business rules and maintain data integrity, while events handle scheduled maintenance tasks without external schedulers.
🎯 Understanding Triggers
What is a Trigger?
A trigger is a named database object that automatically executes when a specified event occurs on a table.
| Trigger Type | Timing | Use Case |
|---|---|---|
| BEFORE INSERT | Before row insertion | Validate data, set defaults |
| AFTER INSERT | After row insertion | Audit logging, cascade updates |
| BEFORE UPDATE | Before row modification | Validate changes, preserve old values |
| AFTER UPDATE | After row modification | Sync related tables, notifications |
| BEFORE DELETE | Before row removal | Prevent deletion, archive data |
| AFTER DELETE | After row removal | Cleanup related data, logging |
💡 Key Insight: Use BEFORE triggers for validation and data modification. Use AFTER triggers for logging and cascading changes to other tables.
🔧 Creating Triggers
Basic Trigger Syntax
DELIMITER //
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
-- Trigger body
-- Access NEW.column for new values (INSERT, UPDATE)
-- Access OLD.column for old values (UPDATE, DELETE)
END //
DELIMITER ;
Example 1: Audit Trail Trigger
-- Create audit table
CREATE TABLE audit_log (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
table_name VARCHAR(64) NOT NULL,
record_id BIGINT NOT NULL,
action ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
old_data JSON,
new_data JSON,
changed_by VARCHAR(100),
changed_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
INDEX idx_table_record (table_name, record_id),
INDEX idx_changed_at (changed_at)
) ENGINE=InnoDB;
-- Trigger for INSERT
DELIMITER //
CREATE TRIGGER trg_users_after_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
INSERT INTO audit_log (
table_name,
record_id,
action,
old_data,
new_data,
changed_by
) VALUES (
'users',
NEW.id,
'INSERT',
NULL,
JSON_OBJECT(
'id', NEW.id,
'username', NEW.username,
'email', NEW.email,
'created_at', NEW.created_at
),
COALESCE(@current_user, USER())
);
END //
DELIMITER ;
-- Trigger for UPDATE
DELIMITER //
CREATE TRIGGER trg_users_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
-- Only log if something actually changed
IF NOT (OLD.username <=> NEW.username
AND OLD.email <=> NEW.email
AND OLD.status <=> NEW.status) THEN
INSERT INTO audit_log (
table_name,
record_id,
action,
old_data,
new_data,
changed_by
) VALUES (
'users',
NEW.id,
'UPDATE',
JSON_OBJECT(
'username', OLD.username,
'email', OLD.email,
'status', OLD.status,
'updated_at', OLD.updated_at
),
JSON_OBJECT(
'username', NEW.username,
'email', NEW.email,
'status', NEW.status,
'updated_at', NEW.updated_at
),
COALESCE(@current_user, USER())
);
END IF;
END //
DELIMITER ;
-- Trigger for DELETE
DELIMITER //
CREATE TRIGGER trg_users_after_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
INSERT INTO audit_log (
table_name,
record_id,
action,
old_data,
new_data,
changed_by
) VALUES (
'users',
OLD.id,
'DELETE',
JSON_OBJECT(
'id', OLD.id,
'username', OLD.username,
'email', OLD.email,
'status', OLD.status
),
NULL,
COALESCE(@current_user, USER())
);
END //
DELIMITER ;
Example 2: Data Validation Trigger
DELIMITER //
CREATE TRIGGER trg_orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
DECLARE v_customer_status VARCHAR(20);
DECLARE v_credit_limit DECIMAL(15,2);
DECLARE v_outstanding_balance DECIMAL(15,2);
-- Validate customer exists and is active
SELECT status, credit_limit
INTO v_customer_status, v_credit_limit
FROM customers
WHERE customer_id = NEW.customer_id;
IF v_customer_status IS NULL THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Customer not found';
END IF;
IF v_customer_status != 'ACTIVE' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Cannot create order for inactive customer';
END IF;
-- Check credit limit
SELECT COALESCE(SUM(total_amount), 0)
INTO v_outstanding_balance
FROM orders
WHERE customer_id = NEW.customer_id
AND status NOT IN ('PAID', 'CANCELLED');
IF (v_outstanding_balance + NEW.total_amount) > v_credit_limit THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Order exceeds customer credit limit';
END IF;
-- Auto-set values
SET NEW.order_date = COALESCE(NEW.order_date, NOW());
SET NEW.status = COALESCE(NEW.status, 'PENDING');
SET NEW.order_number = CONCAT(
'ORD-',
DATE_FORMAT(NOW(), '%Y%m%d'),
'-',
LPAD((SELECT COALESCE(MAX(id), 0) + 1 FROM orders), 6, '0')
);
END //
DELIMITER ;
Example 3: Maintaining Computed Columns
-- Auto-update order totals
DELIMITER //
CREATE TRIGGER trg_order_items_after_insert
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
UPDATE orders
SET
subtotal = (
SELECT COALESCE(SUM(line_total), 0)
FROM order_items
WHERE order_id = NEW.order_id
),
item_count = (
SELECT COUNT(*)
FROM order_items
WHERE order_id = NEW.order_id
),
total_amount = subtotal + tax_amount - discount_amount,
updated_at = NOW()
WHERE order_id = NEW.order_id;
END //
CREATE TRIGGER trg_order_items_after_update
AFTER UPDATE ON order_items
FOR EACH ROW
BEGIN
UPDATE orders
SET
subtotal = (
SELECT COALESCE(SUM(line_total), 0)
FROM order_items
WHERE order_id = NEW.order_id
),
total_amount = subtotal + tax_amount - discount_amount,
updated_at = NOW()
WHERE order_id = NEW.order_id;
END //
CREATE TRIGGER trg_order_items_after_delete
AFTER DELETE ON order_items
FOR EACH ROW
BEGIN
UPDATE orders
SET
subtotal = (
SELECT COALESCE(SUM(line_total), 0)
FROM order_items
WHERE order_id = OLD.order_id
),
item_count = (
SELECT COUNT(*)
FROM order_items
WHERE order_id = OLD.order_id
),
total_amount = subtotal + tax_amount - discount_amount,
updated_at = NOW()
WHERE order_id = OLD.order_id;
END //
DELIMITER ;
📅 MySQL Events (Scheduled Tasks)
What are Events?
MySQL Events are scheduled tasks that run automatically at specified intervals, similar to cron jobs but managed entirely within the database.
Enable the Event Scheduler
-- Check if event scheduler is enabled
SHOW VARIABLES LIKE 'event_scheduler';
-- Enable event scheduler
SET GLOBAL event_scheduler = ON;
-- To make permanent, add to my.cnf:
-- [mysqld]
-- event_scheduler = ON
-- View event scheduler status
SHOW PROCESSLIST; -- Look for 'event_scheduler' thread
Basic Event Syntax
DELIMITER //
CREATE EVENT event_name
ON SCHEDULE
{AT timestamp | EVERY interval [STARTS timestamp] [ENDS timestamp]}
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'description']
DO
BEGIN
-- Event body (SQL statements)
END //
DELIMITER ;
📦 Production Event Examples
Example 1: Data Archival Event
DELIMITER //
CREATE EVENT evt_archive_old_orders
ON SCHEDULE EVERY 1 DAY
STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 2 HOUR) -- Run at 2 AM
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Archives orders older than 2 years to archive table'
DO
BEGIN
DECLARE v_archived_count INT DEFAULT 0;
DECLARE v_batch_size INT DEFAULT 10000;
DECLARE v_cutoff_date DATE;
DECLARE v_start_time DATETIME;
SET v_start_time = NOW();
SET v_cutoff_date = DATE_SUB(CURRENT_DATE, INTERVAL 2 YEAR);
-- Log start
INSERT INTO event_log (event_name, status, message, created_at)
VALUES ('evt_archive_old_orders', 'STARTED',
CONCAT('Archiving orders before ', v_cutoff_date), NOW());
-- Archive in batches to avoid long locks
archive_loop: LOOP
-- Start transaction for each batch
START TRANSACTION;
-- Insert into archive (batch)
INSERT INTO orders_archive
SELECT o.*
FROM orders o
WHERE o.order_date < v_cutoff_date
AND o.status IN ('COMPLETED', 'CANCELLED')
AND NOT EXISTS (
SELECT 1 FROM orders_archive a WHERE a.id = o.id
)
LIMIT v_batch_size;
SET v_archived_count = v_archived_count + ROW_COUNT();
-- If no more rows, exit loop
IF ROW_COUNT() = 0 THEN
COMMIT;
LEAVE archive_loop;
END IF;
-- Delete archived orders from main table
DELETE o FROM orders o
INNER JOIN orders_archive a ON o.id = a.id
WHERE o.order_date < v_cutoff_date
LIMIT v_batch_size;
COMMIT;
-- Small delay to reduce load
DO SLEEP(0.1);
END LOOP;
-- Log completion
INSERT INTO event_log (event_name, status, message, duration_seconds, created_at)
VALUES ('evt_archive_old_orders', 'COMPLETED',
CONCAT('Archived ', v_archived_count, ' orders'),
TIMESTAMPDIFF(SECOND, v_start_time, NOW()), NOW());
END //
DELIMITER ;
Example 2: Statistics Aggregation Event
DELIMITER //
CREATE EVENT evt_refresh_daily_stats
ON SCHEDULE EVERY 1 HOUR
STARTS (TIMESTAMP(CURRENT_DATE))
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Refreshes daily statistics tables every hour'
DO
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
INSERT INTO event_log (event_name, status, message, created_at)
VALUES ('evt_refresh_daily_stats', 'ERROR',
'Failed to refresh statistics', NOW());
END;
-- Refresh today's sales summary
REPLACE INTO daily_sales_summary (
summary_date,
total_orders,
total_revenue,
avg_order_value,
unique_customers,
top_product_id,
updated_at
)
SELECT
CURRENT_DATE,
COUNT(*) AS total_orders,
SUM(total_amount) AS total_revenue,
AVG(total_amount) AS avg_order_value,
COUNT(DISTINCT customer_id) AS unique_customers,
(SELECT product_id
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
WHERE DATE(o.order_date) = CURRENT_DATE
GROUP BY product_id
ORDER BY SUM(quantity) DESC
LIMIT 1) AS top_product_id,
NOW()
FROM orders
WHERE DATE(order_date) = CURRENT_DATE
AND status NOT IN ('CANCELLED', 'REFUNDED');
-- Refresh product performance
REPLACE INTO product_daily_performance (
product_id,
stat_date,
units_sold,
revenue,
view_count,
conversion_rate,
updated_at
)
SELECT
p.id,
CURRENT_DATE,
COALESCE(SUM(oi.quantity), 0) AS units_sold,
COALESCE(SUM(oi.line_total), 0) AS revenue,
COALESCE(pv.view_count, 0) AS view_count,
CASE
WHEN COALESCE(pv.view_count, 0) > 0
THEN COALESCE(COUNT(DISTINCT o.id), 0) / pv.view_count * 100
ELSE 0
END AS conversion_rate,
NOW()
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.id
AND DATE(o.order_date) = CURRENT_DATE
AND o.status NOT IN ('CANCELLED', 'REFUNDED')
LEFT JOIN (
SELECT product_id, COUNT(*) AS view_count
FROM product_views
WHERE DATE(viewed_at) = CURRENT_DATE
GROUP BY product_id
) pv ON p.id = pv.product_id
GROUP BY p.id;
END //
DELIMITER ;
Example 3: Cleanup and Maintenance Event
DELIMITER //
CREATE EVENT evt_database_maintenance
ON SCHEDULE EVERY 1 DAY
STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 3 HOUR) -- 3 AM
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Daily database maintenance tasks'
DO
BEGIN
DECLARE v_start_time DATETIME;
DECLARE v_deleted_count INT DEFAULT 0;
DECLARE v_task VARCHAR(100);
SET v_start_time = NOW();
-- Task 1: Clean expired sessions
SET v_task = 'Clean expired sessions';
DELETE FROM user_sessions
WHERE expires_at < NOW()
LIMIT 50000;
SET v_deleted_count = ROW_COUNT();
INSERT INTO event_log (event_name, status, message, created_at)
VALUES ('evt_database_maintenance', 'PROGRESS',
CONCAT(v_task, ': deleted ', v_deleted_count, ' rows'), NOW());
-- Task 2: Clean old log entries
SET v_task = 'Clean old logs';
DELETE FROM application_logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
LIMIT 100000;
SET v_deleted_count = ROW_COUNT();
INSERT INTO event_log (event_name, status, message, created_at)
VALUES ('evt_database_maintenance', 'PROGRESS',
CONCAT(v_task, ': deleted ', v_deleted_count, ' rows'), NOW());
-- Task 3: Clean temporary data
SET v_task = 'Clean temporary data';
DELETE FROM temp_calculations
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 DAY)
LIMIT 50000;
SET v_deleted_count = ROW_COUNT();
INSERT INTO event_log (event_name, status, message, created_at)
VALUES ('evt_database_maintenance', 'PROGRESS',
CONCAT(v_task, ': deleted ', v_deleted_count, ' rows'), NOW());
-- Task 4: Update statistics on large tables (optional)
-- ANALYZE TABLE orders, order_items, products;
-- Log completion
INSERT INTO event_log (event_name, status, message, duration_seconds, created_at)
VALUES ('evt_database_maintenance', 'COMPLETED',
'All maintenance tasks completed',
TIMESTAMPDIFF(SECOND, v_start_time, NOW()), NOW());
END //
DELIMITER ;
Example 4: Real-Time Alert Event
DELIMITER //
CREATE EVENT evt_monitor_system_health
ON SCHEDULE EVERY 5 MINUTE
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Monitors system health and creates alerts'
DO
BEGIN
DECLARE v_connection_count INT;
DECLARE v_connection_max INT;
DECLARE v_slow_query_count INT;
DECLARE v_disk_usage_pct DECIMAL(5,2);
-- Check connection usage
SELECT VARIABLE_VALUE INTO v_connection_count
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Threads_connected';
SELECT VARIABLE_VALUE INTO v_connection_max
FROM performance_schema.global_variables
WHERE VARIABLE_NAME = 'max_connections';
IF (v_connection_count / v_connection_max * 100) > 80 THEN
INSERT INTO system_alerts (
alert_type,
severity,
message,
metric_value,
threshold_value,
created_at
) VALUES (
'HIGH_CONNECTIONS',
'WARNING',
CONCAT('Connection usage at ',
ROUND(v_connection_count / v_connection_max * 100, 1), '%'),
v_connection_count,
v_connection_max * 0.8,
NOW()
);
END IF;
-- Check for slow queries in last 5 minutes
SELECT COUNT(*) INTO v_slow_query_count
FROM mysql.slow_log
WHERE start_time > DATE_SUB(NOW(), INTERVAL 5 MINUTE);
IF v_slow_query_count > 10 THEN
INSERT INTO system_alerts (
alert_type,
severity,
message,
metric_value,
threshold_value,
created_at
) VALUES (
'SLOW_QUERIES',
'WARNING',
CONCAT(v_slow_query_count, ' slow queries in last 5 minutes'),
v_slow_query_count,
10,
NOW()
);
END IF;
-- Cleanup old alerts (keep 7 days)
DELETE FROM system_alerts
WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY)
AND acknowledged = TRUE;
END //
DELIMITER ;
📊 Managing Triggers and Events
Viewing Triggers
-- List all triggers in a database
SHOW TRIGGERS FROM database_name;
-- View trigger details
SELECT
TRIGGER_NAME,
EVENT_MANIPULATION,
EVENT_OBJECT_TABLE,
ACTION_TIMING,
ACTION_STATEMENT
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'your_database'
ORDER BY EVENT_OBJECT_TABLE, ACTION_TIMING, EVENT_MANIPULATION;
-- Show create statement for a trigger
SHOW CREATE TRIGGER trigger_name;
Viewing Events
-- List all events
SHOW EVENTS FROM database_name;
-- View event details
SELECT
EVENT_NAME,
EVENT_TYPE,
EXECUTE_AT,
INTERVAL_VALUE,
INTERVAL_FIELD,
LAST_EXECUTED,
STATUS,
EVENT_COMMENT
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = 'your_database';
-- Show create statement for an event
SHOW CREATE EVENT event_name;
Modifying and Dropping
-- Drop trigger
DROP TRIGGER IF EXISTS trigger_name;
-- Drop event
DROP EVENT IF EXISTS event_name;
-- Disable event temporarily
ALTER EVENT event_name DISABLE;
-- Enable event
ALTER EVENT event_name ENABLE;
-- Modify event schedule
ALTER EVENT event_name
ON SCHEDULE EVERY 2 HOUR;
⚠️ Common Issues and Solutions
Issue 1: Trigger Causing Deadlocks
-- Problem: Trigger updates another table that causes deadlocks
-- Solution: Use consistent ordering and minimal locking
DELIMITER //
CREATE TRIGGER trg_safe_inventory_update
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
-- Use explicit ordering to prevent deadlocks
UPDATE products
SET
stock_quantity = stock_quantity - NEW.quantity,
updated_at = NOW()
WHERE product_id = NEW.product_id
AND stock_quantity >= NEW.quantity; -- Optimistic check
IF ROW_COUNT() = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Insufficient stock or product not found';
END IF;
END //
DELIMITER ;
Issue 2: Recursive Trigger Calls
-- MySQL doesn't support recursive triggers by default
-- But you can accidentally create infinite loops with cascading triggers
-- Solution: Use a session variable to prevent recursion
DELIMITER //
CREATE TRIGGER trg_prevent_recursion
AFTER UPDATE ON table_a
FOR EACH ROW
BEGIN
-- Check if we're already in a trigger
IF @trigger_disabled IS NULL OR @trigger_disabled = 0 THEN
SET @trigger_disabled = 1;
-- Your trigger logic here
UPDATE table_b SET column = value WHERE id = NEW.related_id;
SET @trigger_disabled = 0;
END IF;
END //
DELIMITER ;
Issue 3: Event Not Running
-- Check event scheduler status
SHOW VARIABLES LIKE 'event_scheduler';
-- Check event status
SELECT EVENT_NAME, STATUS, LAST_EXECUTED
FROM information_schema.EVENTS
WHERE EVENT_NAME = 'your_event_name';
-- Check for errors
SHOW WARNINGS;
-- Debug by running event body manually
-- (Copy the DO BEGIN...END block and execute)
🚀 Performance Considerations
Trigger Performance Tips
-- ✅ DO: Keep triggers lightweight
CREATE TRIGGER trg_fast
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
-- Quick update only
UPDATE customers
SET last_order_date = NOW()
WHERE customer_id = NEW.customer_id;
END;
-- ❌ DON'T: Heavy operations in triggers
CREATE TRIGGER trg_slow
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
-- Avoid: Complex aggregations
UPDATE customers SET
total_orders = (SELECT COUNT(*) FROM orders WHERE customer_id = NEW.customer_id),
total_spent = (SELECT SUM(total) FROM orders WHERE customer_id = NEW.customer_id)
WHERE customer_id = NEW.customer_id;
END;
-- ✅ BETTER: Use incremental updates
CREATE TRIGGER trg_incremental
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE customers SET
total_orders = total_orders + 1,
total_spent = total_spent + NEW.total_amount,
last_order_date = NOW()
WHERE customer_id = NEW.customer_id;
END;
Event Performance Tips
-- Use batching for large data operations
DELIMITER //
CREATE EVENT evt_batch_cleanup
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
DECLARE v_rows_affected INT DEFAULT 1;
DECLARE v_total INT DEFAULT 0;
WHILE v_rows_affected > 0 AND v_total < 1000000 DO
DELETE FROM large_log_table
WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
LIMIT 10000;
SET v_rows_affected = ROW_COUNT();
SET v_total = v_total + v_rows_affected;
-- Prevent monopolizing resources
IF v_rows_affected > 0 THEN
DO SLEEP(0.5);
END IF;
END WHILE;
END //
DELIMITER ;
✅ Best Practices Checklist
Triggers
- [ ] Keep trigger logic simple and fast
- [ ] Use BEFORE triggers for validation, AFTER for logging
- [ ] Avoid calling stored procedures from triggers when possible
- [ ] Document all triggers and their purpose
- [ ] Test triggers with edge cases
- [ ] Monitor trigger execution time
Events
- [ ] Enable event scheduler in production
- [ ] Add comprehensive error handling
- [ ] Log event execution start and end
- [ ] Use batching for large data operations
- [ ] Schedule heavy events during off-peak hours
- [ ] Monitor event execution history
🎓 Conclusion
MySQL triggers and events provide powerful automation capabilities directly within the database. When used appropriately, they can enforce data integrity, maintain audit trails, and automate routine maintenance tasks without relying on external tools.
💡 Pro Tip: Balance trigger usage carefully. While they ensure data consistency, too many complex triggers can impact write performance. For complex business logic, consider using stored procedures called from your application instead.
About the Author
Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in MySQL, SQL optimization, and enterprise database solutions.
Related Articles
- MySQL Stored Procedures Best Practices
- MySQL Performance Schema Monitoring
- MySQL Security User Management and Privileges
Need help with MySQL automation? Contact us for expert database consulting.