OceanBase 触发器和事件

触发器

触发器类型

1. 行级触发器：针对每一行数据的变化触发
2. 语句级触发器：针对整个语句触发，无论影响多少行
3. BEFORE 触发器：在事件发生前触发
4. AFTER 触发器：在事件发生后触发
5. INSTEAD OF 触发器：替代原操作执行

触发器事件

1. INSERT：插入数据时触发
2. UPDATE：更新数据时触发
3. DELETE：删除数据时触发
4. LOAD：加载数据时触发

创建触发器

-- 创建 BEFORE INSERT 触发器
CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
    SET NEW.created_at = CURRENT_TIMESTAMP;
    SET NEW.updated_at = CURRENT_TIMESTAMP;
END;

-- 创建 AFTER UPDATE 触发器
CREATE TRIGGER trigger_name
AFTER UPDATE ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
    SET NEW.updated_at = CURRENT_TIMESTAMP;
    -- 记录审计日志
    INSERT INTO audit_log (table_name, operation, old_value, new_value, operated_at) 
    VALUES ('table_name', 'UPDATE', OLD.column_name, NEW.column_name, CURRENT_TIMESTAMP);
END;

-- 创建语句级触发器
CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
BEGIN
    -- 触发器逻辑
    INSERT INTO statistics_table (table_name, insert_count, operated_at) 
    VALUES ('table_name', ROW_COUNT(), CURRENT_TIMESTAMP);
END;

查看触发器

-- 查看所有触发器
SHOW TRIGGERS;

-- 查看特定表的触发器
SHOW TRIGGERS LIKE 'table_name';

-- 查看触发器的详细定义
SELECT * FROM information_schema.triggers WHERE trigger_name = 'trigger_name';

修改触发器

-- 修改触发器（需要先删除再创建）
DROP TRIGGER IF EXISTS trigger_name;
CREATE TRIGGER trigger_name
-- 新的触发器定义

删除触发器

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

-- 删除特定表的所有触发器
DROP TRIGGER IF EXISTS table_name.*;

事件

事件调度器

1. 启用事件调度器：

   SET GLOBAL event_scheduler = ON;

2. 查看事件调度器状态：

   SHOW VARIABLES LIKE 'event_scheduler';

创建事件

-- 创建简单事件
CREATE EVENT event_name
ON SCHEDULE EVERY 1 HOUR
DO
    -- 事件逻辑
    DELETE FROM log_table WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 创建带开始时间和结束时间的事件
CREATE EVENT event_name
ON SCHEDULE EVERY 1 DAY
STARTS '2023-01-01 00:00:00'
ENDS '2023-12-31 23:59:59'
DO
    -- 事件逻辑
    UPDATE statistics_table SET daily_count = 0 WHERE DATE(stat_date) = DATE(NOW());

-- 创建一次性事件
CREATE EVENT event_name
ON SCHEDULE AT '2023-01-01 00:00:00'
DO
    -- 事件逻辑
    INSERT INTO holiday_table (holiday_date, description) VALUES ('2023-01-01', 'New Year');

查看事件

-- 查看所有事件
SHOW EVENTS;

-- 查看特定数据库的事件
SHOW EVENTS FROM database_name;

-- 查看事件的详细定义
SELECT * FROM information_schema.events WHERE event_name = 'event_name';

修改事件

-- 修改事件
ALTER EVENT event_name
ON SCHEDULE EVERY 2 HOUR
ENABLE;

-- 禁用事件
ALTER EVENT event_name DISABLE;

-- 启用事件
ALTER EVENT event_name ENABLE;

-- 修改事件的 SQL 语句
ALTER EVENT event_name
DO
    -- 新的事件逻辑
    DELETE FROM log_table WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

删除事件

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

-- 删除特定数据库的所有事件
DROP EVENT IF EXISTS database_name.*;

触发器和事件的使用场景

触发器使用场景

1. 数据一致性维护：确保相关表之间的数据一致性
2. 审计日志记录：自动记录数据的变更历史
3. 业务规则 enforcement：强制执行业务规则，如数据格式验证
4. 自动计算字段：自动计算派生字段的值
5. 级联操作：实现级联更新或删除

事件使用场景

1. 定期数据清理：定期删除过期数据
2. 统计数据更新：定期更新统计信息
3. 数据备份：定期执行数据备份
4. 报表生成：定期生成业务报表
5. 系统维护：定期执行系统维护任务

触发器和事件最佳实践

触发器最佳实践

1. 保持触发器简洁：触发器逻辑应尽量简洁，避免复杂的业务逻辑
2. 避免递归触发：防止触发器相互调用导致无限递归
3. 使用 AFTER 触发器：优先使用 AFTER 触发器，避免影响原操作性能
4. 考虑性能影响：触发器会增加 DML 操作的开销，应谨慎使用
5. 测试充分：在生产环境使用前，充分测试触发器的逻辑和性能

事件最佳实践

1. 合理设置调度频率：根据业务需求设置合适的事件调度频率
2. 考虑系统负载：避免在业务高峰期执行资源密集型事件
3. 设置事件超时：为长时间运行的事件设置超时时间
4. 记录事件执行日志：记录事件的执行情况，便于调试和监控
5. 测试充分：在生产环境使用前，充分测试事件的逻辑和性能

安全性最佳实践

1. 严格权限控制：只有具有 CREATE TRIGGER 和 CREATE EVENT 权限的用户才能创建触发器和事件
2. 使用最小权限原则：为触发器和事件使用的账户分配最小必要权限
3. 定期审计：定期审计触发器和事件，确保其逻辑安全可靠
4. 防止 SQL 注入：在触发器和事件中使用参数化查询，防止 SQL 注入

触发器和事件的性能考虑

触发器性能影响

1. 增加 DML 操作开销：触发器会在 DML 操作执行前后执行额外的 SQL 语句
2. 可能导致锁竞争：复杂的触发器逻辑可能导致锁竞争，影响并发性能
3. 影响批量操作性能：对于批量插入、更新或删除操作，触发器会针对每一行执行，影响性能

事件性能影响

1. 资源消耗：长时间运行的事件会消耗系统资源
2. 调度开销：频繁执行的事件会增加调度器的开销
3. 并发问题：多个事件同时执行可能导致资源竞争

性能优化建议

1. 优化触发器逻辑：简化触发器逻辑，减少不必要的操作
2. 使用存储过程：将复杂逻辑封装到存储过程中，减少触发器的开销
3. 调整事件调度频率：根据业务需求调整事件的调度频率
4. 优化事件 SQL：优化事件中执行的 SQL 语句，提高执行效率
5. 考虑异步执行：对于资源密集型操作，考虑使用异步方式执行

常见问题（FAQ）

Q1: 触发器可以调用存储过程吗？

A1: 是的，触发器可以调用存储过程。在触发器中调用存储过程可以简化触发器逻辑，提高代码的可维护性。

-- 创建存储过程
CREATE PROCEDURE audit_log(
    IN p_table_name VARCHAR(50),
    IN p_operation VARCHAR(10),
    IN p_old_value VARCHAR(255),
    IN p_new_value VARCHAR(255)
)
BEGIN
    INSERT INTO audit_log (table_name, operation, old_value, new_value, operated_at) 
    VALUES (p_table_name, p_operation, p_old_value, p_new_value, CURRENT_TIMESTAMP);
END;

-- 创建调用存储过程的触发器
CREATE TRIGGER trigger_name
AFTER UPDATE ON table_name
FOR EACH ROW
BEGIN
    CALL audit_log('table_name', 'UPDATE', OLD.column_name, NEW.column_name);
END;

Q2: 事件调度器不执行怎么办？

A2: 事件调度器不执行可能有以下原因：

1. 事件调度器未启用
2. 事件被禁用
3. 事件的调度时间未到
4. 事件的 SQL 语句执行失败
5. 事件的权限不足

-- 检查事件调度器状态
SHOW VARIABLES LIKE 'event_scheduler';

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

-- 检查事件状态
SELECT event_name, status FROM information_schema.events WHERE event_name = 'event_name';

-- 启用事件
ALTER EVENT event_name ENABLE;

Q3: 如何查看触发器的执行日志？

A3: OceanBase 不会直接记录触发器的执行日志，但可以在触发器中添加日志记录语句，将执行情况写入日志表：

-- 创建日志表
CREATE TABLE trigger_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    trigger_name VARCHAR(50),
    table_name VARCHAR(50),
    operation VARCHAR(10),
    gmt_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    details TEXT
);

-- 在触发器中添加日志记录
CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
    -- 记录日志
    INSERT INTO trigger_log (trigger_name, table_name, operation, details) 
    VALUES ('trigger_name', 'table_name', 'INSERT', CONCAT('Inserted row with id:', NEW.id));
END;

Q4: 触发器和事件的区别是什么？

A4: 触发器和事件的主要区别：

1. 触发条件：触发器由 DML 操作触发，事件由时间触发
2. 执行时机：触发器在 DML 操作执行前后执行，事件在指定时间点执行
3. 作用范围：触发器针对特定表，事件针对整个数据库
4. 使用场景：触发器用于数据一致性维护、审计日志等，事件用于定期任务

Q5: 如何优化触发器和事件的性能？

A5: 可以通过以下方式优化触发器和事件的性能：

1. 简化触发器逻辑，减少不必要的操作
2. 使用存储过程封装复杂逻辑
3. 调整事件调度频率，避免频繁执行
4. 优化 SQL 语句，提高执行效率
5. 考虑使用异步方式执行资源密集型操作
6. 避免在触发器中执行长时间运行的操作

Q6: 触发器可以在视图上创建吗？

A6: 是的，可以在视图上创建 INSTEAD OF 触发器，用于替代对视图的直接操作：

-- 创建视图
CREATE VIEW view_name AS SELECT * FROM table_name;

-- 创建 INSTEAD OF 触发器
CREATE TRIGGER trigger_name
INSTEAD OF INSERT ON view_name
FOR EACH ROW
BEGIN
    -- 替代插入逻辑
    INSERT INTO table_name (column1, column2) VALUES (NEW.column1, NEW.column2);
END;