Triggers، Views و Events

۷.۱ Triggers – چه هستند؟

Trigger یک قطعه کد است که خودکار هنگام رخ‌دادن یک رویداد (INSERT، UPDATE، DELETE) روی جدول اجرا می‌شود. کاربردهای رایج:

• ثبت Audit Log (چه کسی کِی چه چیزی را تغییر داد)
• به‌روزرسانی خودکار ستون‌های محاسبه‌شده
• اعتبارسنجی سفارشی (مثل بررسی قوانین business)
• denormalization خودکار (sync کردن داده بین جدول‌ها)

۷.۲ ساختار Trigger

SQL📋 کپی
DELIMITER $$

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- کد
END$$

DELIMITER ;

درون trigger، دو شیء جادویی در دسترس است:

• NEW: ردیف جدید (در INSERT و UPDATE)
• OLD: ردیف قدیمی (در UPDATE و DELETE)

۷.۳ مثال عملی: Audit Log

یکی از پرکاربردترین موارد: ثبت تغییرات قیمت محصولات.

SQL📋 کپی
-- جدول لاگ
CREATE TABLE product_price_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id INT UNSIGNED NOT NULL,
    old_price BIGINT UNSIGNED,
    new_price BIGINT UNSIGNED,
    changed_by VARCHAR(80),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_product (product_id, changed_at)
) ENGINE=InnoDB;

DELIMITER $$

CREATE TRIGGER products_price_audit
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    -- فقط وقتی قیمت تغییر کرد لاگ بگیر
    IF OLD.price <> NEW.price THEN
        INSERT INTO product_price_log
            (product_id, old_price, new_price, changed_by)
        VALUES
            (NEW.id, OLD.price, NEW.price, CURRENT_USER());
    END IF;
END$$

DELIMITER ;

-- آزمون
UPDATE products SET price = 1200000 WHERE id = 5;
SELECT * FROM product_price_log;

۷.۴ مثال: همگام‌سازی Counter

SQL📋 کپی
-- جدول products یک comments_count دارد
-- هر بار comment اضافه/حذف شود، counter باید به‌روز شود

DELIMITER $$

CREATE TRIGGER comments_after_insert
AFTER INSERT ON product_comments
FOR EACH ROW
BEGIN
    UPDATE products
    SET comments_count = comments_count + 1
    WHERE id = NEW.product_id;
END$$

CREATE TRIGGER comments_after_delete
AFTER DELETE ON product_comments
FOR EACH ROW
BEGIN
    UPDATE products
    SET comments_count = GREATEST(comments_count - 1, 0)
    WHERE id = OLD.product_id;
END$$

DELIMITER ;

۷.۵ BEFORE Trigger – اعتبارسنجی و اصلاح داده

SQL📋 کپی
DELIMITER $$

-- نرمال‌سازی شماره موبایل قبل از insert
CREATE TRIGGER users_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    -- حذف فاصله، +98 به 0 تبدیل
    SET NEW.phone = REPLACE(NEW.phone, ' ', '');
    IF LEFT(NEW.phone, 3) = '+98' THEN
        SET NEW.phone = CONCAT('0', SUBSTRING(NEW.phone, 4));
    END IF;
    -- ساخت slug خودکار
    IF NEW.slug IS NULL OR NEW.slug = '' THEN
        SET NEW.slug = LOWER(REPLACE(NEW.first_name, ' ', '-'));
    END IF;
END$$

-- جلوگیری از قیمت منفی
CREATE TRIGGER products_before_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.price < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'قیمت نمی‌تواند منفی باشد';
    END IF;
END$$

DELIMITER ;

۷.۶ Views - جدول مجازی

View یک Query ذخیره شده است که مثل جدول قابل استفاده می‌شود. هر بار SELECT روی آن می‌کنید، Query زیربنایی اجرا می‌شود.

SQL📋 کپی
-- View ساده
CREATE VIEW active_products AS
SELECT id, title, price, stock
FROM products
WHERE is_active = 1 AND stock > 0;

-- استفاده مثل جدول
SELECT * FROM active_products WHERE price < 500000;

-- View با JOIN (پر کاربرد)
CREATE VIEW order_summary AS
SELECT
    o.id, o.order_number, o.created_at,
    u.first_name, u.last_name, u.phone,
    o.total, o.status
FROM orders o
INNER JOIN users u ON u.id = o.user_id;

-- View با aggregate
CREATE VIEW daily_sales AS
SELECT
    DATE(created_at) AS sale_date,
    COUNT(*) AS orders_count,
    SUM(total) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY DATE(created_at);

مزایای View

• سادگی: Query پیچیده را پشت یک نام پنهان می‌کند
• امنیت: کاربر فقط به view دسترسی دارد، نه جدول‌های اصلی
• سازگاری: اگر schema تغییر کرد، فقط view را بازنویسی می‌کنید

۷.۷ Updatable Views

اگر view ساده باشد، می‌توانید روی آن INSERT/UPDATE هم بزنید:

SQL📋 کپی
CREATE VIEW persian_products AS
SELECT id, title, price
FROM products
WHERE language = 'fa'
WITH CHECK OPTION;  -- جلوی INSERT داده‌ای که شرط را نقض کند را می‌گیرد

-- این کار می‌کند:
UPDATE persian_products SET price = 200000 WHERE id = 5;

-- این خطا می‌دهد چون language='en' شرط view را نقض می‌کند:
INSERT INTO persian_products (id, title, price, language) VALUES (...);

محدودیت‌های Updatable View:

• نباید DISTINCT، GROUP BY، HAVING، UNION داشته باشد
• نباید subquery در SELECT list داشته باشد
• نباید Aggregate function داشته باشد

۷.۸ Materialized View - شبیه‌سازی در MySQL

برخلاف PostgreSQL، MySQL/MariaDB به‌صورت native از Materialized View پشتیبانی نمی‌کند. اما می‌توانیم با ترکیب Table و Event شبیه‌سازی کنیم:

SQL📋 کپی
-- جدول cache
CREATE TABLE mv_daily_sales (
    sale_date DATE PRIMARY KEY,
    orders_count INT,
    revenue BIGINT,
    refreshed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Procedure برای refresh
DELIMITER $$
CREATE PROCEDURE refresh_daily_sales()
BEGIN
    -- پاک کردن داده‌های قدیمی
    TRUNCATE TABLE mv_daily_sales;

    -- پر کردن از داده فعلی
    INSERT INTO mv_daily_sales (sale_date, orders_count, revenue)
    SELECT
        DATE(created_at),
        COUNT(*),
        SUM(total)
    FROM orders
    WHERE status = 'paid'
    GROUP BY DATE(created_at);
END$$
DELIMITER ;

-- Event برای اجرای خودکار (در ادامه فصل می‌بینیم)
CREATE EVENT refresh_mv_daily_sales
ON SCHEDULE EVERY 1 HOUR
DO CALL refresh_daily_sales();

۷.۹ Events - زمان‌بند داخلی MySQL

Events مثل cron job داخل خود MySQL هستند. برای کارهای زمان‌بندی شده عالی‌اند.

فعال‌سازی Event Scheduler

SQL📋 کپی
-- بررسی وضعیت
SHOW VARIABLES LIKE 'event_scheduler';

-- فعال‌سازی runtime
SET GLOBAL event_scheduler = ON;

-- در my.cnf برای persistent
[mysqld]
event_scheduler = ON

ساخت Events

SQL📋 کپی
DELIMITER $$

-- یک‌بار، در زمان مشخص
CREATE EVENT cleanup_temp_files
ON SCHEDULE AT '2026-12-31 23:59:00'
DO
BEGIN
    DELETE FROM temp_uploads WHERE created_at < NOW() - INTERVAL 30 DAY;
END$$

-- تکرارشونده هر ساعت
CREATE EVENT refresh_stats
ON SCHEDULE EVERY 1 HOUR
DO
    CALL refresh_daily_sales();$$

-- تکرار با شروع و پایان مشخص
CREATE EVENT daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS '2026-05-02 03:00:00'
ENDS '2027-01-01 00:00:00'
DO
BEGIN
    DELETE FROM logs WHERE created_at < NOW() - INTERVAL 90 DAY;
    DELETE FROM sessions WHERE expires_at < NOW();
    OPTIMIZE TABLE logs;
END$$

DELIMITER ;

-- مدیریت
SHOW EVENTS;
SHOW EVENTS FROM my_database;
ALTER EVENT daily_cleanup DISABLE;
ALTER EVENT daily_cleanup ENABLE;
DROP EVENT daily_cleanup;

کاربردهای رایج

• پاک کردن داده‌های منقضی (sessions، logs قدیمی)
• refresh کردن جدول‌های cache
• محاسبه آمار شبانه
• backup داده‌های مهم به جدول archive
• بستن سفارش‌های pending که بیش از ۲۴ ساعت پرداخت نشده‌اند

۷.۱۰ مدیریت و Debug

SQL📋 کپی
-- لیست همه triggerها
SHOW TRIGGERS FROM my_database;

SELECT trigger_name, event_object_table, action_timing, event_manipulation
FROM information_schema.triggers
WHERE trigger_schema = 'my_database';

-- نمایش کد یک trigger
SHOW CREATE TRIGGER products_price_audit;

-- لیست همه viewها
SELECT table_name AS view_name
FROM information_schema.views
WHERE table_schema = 'my_database';

SHOW CREATE VIEW active_products;

-- لیست همه events
SHOW EVENTS;
SELECT event_name, status, last_executed
FROM information_schema.events
WHERE event_schema = 'my_database';

۷.۱۱ خلاصه فصل

• Triggerها برای audit log و cache counter عالی‌اند، اما performance را در نظر بگیرید
• BEFORE برای validation و normalization، AFTER برای ثبت لاگ
• Views پیچیدگی Query را پنهان می‌کنند، اما Performance را بهبود نمی‌دهند
• Events جایگزین cron job داخل دیتابیس‌اند؛ event_scheduler را فعال کنید
• Materialized View با ترکیب Table + Event شبیه‌سازی می‌شود