🔹 Triggers in SQL
A trigger is a stored procedure that automatically executes (fires) in response to certain events on a table (like INSERT, UPDATE, or DELETE).
Example: Trigger for Audit Logging
Suppose you have a table employees and you want to log changes whenever an employee’s salary is updated.
-- Create employees table
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10,2)
);
-- Audit log table
CREATE TABLE salary_audit (
audit_id INT AUTO_INCREMENT PRIMARY KEY,
emp_id INT,
old_salary DECIMAL(10,2),
new_salary DECIMAL(10,2),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create trigger
CREATE TRIGGER after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO salary_audit (emp_id, old_salary, new_salary)
VALUES (OLD.emp_id, OLD.salary, NEW.salary);
END;
✅ Now, whenever you run:
UPDATE employees SET salary = 60000 WHERE emp_id = 1;
The salary_audit table will automatically record the change.
🔹 Events in SQL (MySQL EVENT Scheduler)
An event is a task that runs according to a schedule (like a cron job, but inside the database).
⚠️ Note: Events are supported in MySQL/MariaDB, but not in all SQL databases (e.g., not in SQL Server or Oracle in the same way).
Example: Automatically Delete Old Records
Suppose you have a logs table and want to delete records older than 30 days every night.
-- Enable event scheduler
SET GLOBAL event_scheduler = ON;
-- Create logs table
CREATE TABLE logs (
log_id INT AUTO_INCREMENT PRIMARY KEY,
message VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create event
CREATE EVENT delete_old_logs
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;
✅ This will run once a day and clean up old log records.
🔑 Key Differences
| Feature | Trigger | Event |
|---|---|---|
| When it runs | On INSERT, UPDATE, or DELETE | On a schedule (time-based) |
| Purpose | Automate auditing, validation, cascading changes | Automate maintenance, scheduling tasks |
| Example use | Logging salary updates | Deleting old records nightly |