CREATE TRIGGER trigger_name
{BEFORE|AFTER} {INSERT|UPDATE|DELETE}
ON table_name FOR EACH ROW
BEGIN
    -- 触发器逻辑
END;

-- NEW 和 OLD 关键字
-- INSERT:  NEW = 新插入行
-- UPDATE:  NEW = 新值, OLD = 旧值
-- DELETE:  OLD = 被删除行

-- 示例1：插入订单时更新用户订单数
DELIMITER //
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE users SET order_count = order_count + 1 WHERE id = NEW.user_id;
END //

-- 示例2：删除订单时回滚库存
CREATE TRIGGER before_order_delete
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    UPDATE products SET stock = stock + OLD.quantity WHERE id = OLD.product_id;
END //

-- 示例3：记录用户状态变更日志
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_change_log (user_id, old_status, new_status, changed_at)
    VALUES (OLD.id, OLD.status, NEW.status, NOW());
END //

-- 示例4：自动设置创建时间
CREATE TRIGGER before_insert_set_time
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    IF NEW.created_at IS NULL THEN
        SET NEW.created_at = NOW();
    END IF;
END //

DELIMITER ;

-- 查看触发器
SHOW TRIGGERS;
SHOW TRIGGERS FROM mydb;

-- 查看触发器定义
SHOW CREATE TRIGGER trigger_name;

-- 删除触发器
DROP TRIGGER IF EXISTS trigger_name;

CREATE EVENT event_name
ON SCHEDULE schedule_type
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
DO event_body;

-- 启用调度器
SET GLOBAL event_scheduler = ON;

-- 每天凌晨2点清理过期日志
CREATE EVENT daily_cleanup
ON SCHEDULE EVERY 1 DAY STARTS '2024-01-01 02:00:00'
DO
    DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 每周一凌晨3点生成报表
CREATE EVENT weekly_report
ON SCHEDULE EVERY 1 WEEK STARTS '2024-01-08 03:00:00'
DO
    INSERT INTO reports (content, created_at) VALUES ('Weekly Report', NOW());

-- 每月第一天归档
CREATE EVENT monthly_archive
ON SCHEDULE AT '2024-02-01 00:00:00'
ON COMPLETION NOT PRESERVE
DO
    INSERT INTO archives SELECT * FROM orders WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- 每隔5分钟执行监控
CREATE EVENT every_5_minutes
ON SCHEDULE EVERY 5 MINUTE
DO
    INSERT INTO monitor (timestamp) VALUES (NOW());

-- 启用/禁用事件
ALTER EVENT event_name ENABLE;
ALTER EVENT event_name DISABLE;

-- 查看事件
SHOW EVENTS;
SHOW EVENTS FROM mydb;

-- 删除事件
DROP EVENT IF EXISTS event_name;

✅ 触发器：表操作时自动执行的逻辑
✅ NEW/OLD：访问新/旧行数据
✅ 事件：定时执行的任务调度