Triggers and event scheduling in SQL help automate database tasks. Triggers automatically execute in response to specific changes in a table, while events run at scheduled times. These features improve data consistency and automate routine tasks like logging, notifications, and cleanup operations.
A trigger is a special type of stored procedure that runs automatically when a specified event occurs in a table. It is commonly used for:
Triggers can be classified based on when they execute and what they act on:
| Type | Description |
|---|---|
| BEFORE Trigger | Executes before an INSERT, UPDATE, or DELETE operation. |
| AFTER Trigger | Executes after the specified operation is completed. |
| INSTEAD OF Trigger | Replaces the default operation with custom logic (used in views). |
| ROW-Level Trigger | Executes once for each row affected by the operation. |
| STATEMENT-Level Trigger | Executes once per statement, regardless of the number of rows affected. |
A trigger that logs changes in an employees table:
CREATE TABLE employee_log (
log_id INT AUTO_INCREMENT PRIMARY KEY,
emp_id INT,
action_type VARCHAR(10),
action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
INSERT INTO employee_log (emp_id, action_type)
VALUES (OLD.id, 'UPDATE');Whenever an UPDATE occurs in the employees table, the change is logged in employee_log.
CREATE TRIGGER prevent_negative_balance
BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
IF NEW.balance < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Balance cannot be negative';
END IF;
END;If an update tries to set a negative balance, the trigger prevents it.
Events are automated tasks that run at scheduled times in MySQL or PostgreSQL. They are useful for:
To use events in MySQL, first enable the scheduler:
SET GLOBAL event_scheduler = ON;An event that deletes inactive users every day:
CREATE EVENT delete_inactive_users
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM users WHERE last_login < NOW() - INTERVAL 90 DAY;This event runs daily and removes users who haven't logged in for 90 days.
CREATE EVENT generate_monthly_report
ON SCHEDULE EVERY 1 MONTH
DO
INSERT INTO reports (report_date, total_sales)
SELECT CURDATE(), SUM(amount) FROM sales;This event calculates the total sales every month and stores it in a reports table.
SHOW EVENTS;ALTER EVENT delete_inactive_users DISABLE;DROP EVENT delete_inactive_users;| Feature | Triggers | Events |
|---|---|---|
| Execution | Runs immediately after a table operation | Runs at a scheduled time |
| Use Cases | Data validation, logging, automation | Cleanup, reports, scheduled tasks |
| Scope | Table-level | Database-wide |
| Control | Cannot be disabled | Can be enabled/disabled |
Triggers and event scheduling are powerful tools for automating database management. Triggers help enforce rules and maintain data integrity, while events handle scheduled tasks. Properly using these features can improve efficiency and reduce manual work.
Sign in to join the discussion and post comments.
Sign in