Triggers and Event Scheduling in SQL

Introduction

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.

What are SQL Triggers?

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:

• Enforcing business rules (e.g., preventing negative bank balances)
• Auditing changes (e.g., logging updates or deletions)
• Maintaining data integrity (e.g., synchronizing data between tables)

Types of Triggers in SQL

Triggers can be classified based on when they execute and what they act on:

Example: Creating a Trigger

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.

Example: Preventing Negative Balance in a Bank System

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.

What is Event Scheduling in SQL?

Events are automated tasks that run at scheduled times in MySQL or PostgreSQL. They are useful for:

• Data cleanup (e.g., deleting old records)
• Sending notifications (e.g., reminders for subscriptions)
• Automating backups

Enabling the Event Scheduler

To use events in MySQL, first enable the scheduler:

SET GLOBAL event_scheduler = ON;

Creating a Scheduled Event

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.

Example: Automating a Monthly Report

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.

Managing Events

• List all events: SHOW EVENTS;
• Disable an event:

ALTER EVENT delete_inactive_users DISABLE;

• Drop an event:

DROP EVENT delete_inactive_users;

Triggers vs. Events: Key Differences

 Conclusion

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.