kingbase教程FG021-kingbase触发器使用与场景实战
目录大纲
内容简介
本文档详细介绍kingbase数据库的触发器使用方法与场景，包括触发器创建、管理、使用和优化等操作。风哥教程参考kingbase官方文档《KingbaseES PL/SQL参考手册》。，风哥提示：
Part01-基础概念与理论知识
1.1 触发器概念与作用
触发器是kingbase数据库中在特定事件发生时自动执行的代码块，主要作用包括：
- 数据验证和约束
- 数据审计和日志记录
- 数据转换和计算
- 级联操作和同步
- 业务规则的强制执行，学习交流加群风哥微信: itpux-com
1.2 触发器类型与触发时机
触发器的类型和触发时机：
- INSERT触发器：在插入数据时触发
- UPDATE触发器：在更新数据时触发
- DELETE触发器：在删除数据时触发
- BEFORE触发器：在操作执行前触发
- AFTER触发器：在操作执行后触发
Part02-生产环境规划与建议
2.1 触发器使用场景
- 数据验证和约束，学习交流加群风哥QQ113257174
- 数据审计和日志记录
- 数据转换和计算
- 级联操作和同步
- 业务规则的强制执行
2.2 触发器设计建议
- 命名规范：使用有意义的触发器名称
- 逻辑简洁：触发器逻辑应该简洁明了
- 性能考虑：避免在触发器中执行复杂操作，更多视频教程www.fgedu.net.cn
- 错误处理：添加适当的异常处理
- 文档注释：添加详细的注释说明
Part03-生产环境项目实施方案
3.1 触发器创建与管理
触发器创建的步骤：
- 编写触发器代码
- 创建触发器
- 测试触发器
- 管理触发器，更多学习教程公众号风哥教程itpux_com
3.2 触发器触发条件
触发器的触发条件：
- 特定的表操作（INSERT、UPDATE、DELETE）
- 特定的列更新（UPDATE OF column_name）
- 特定的条件（WHEN condition）
Part04-生产案例与实战讲解
4.1 触发器创建实战
创建触发器：
# 创建审计表
su – kingbase -c “psql -d fgedudb -c ‘CREATE TABLE fgedu_user_audit (id SERIAL PRIMARY KEY, user_id INTEGER, action VARCHAR(20), old_value TEXT, new_value TEXT, action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP);'”
CREATE TABLE
# 创建触发器函数
su – kingbase -c “psql -d fgedudb -c ‘CREATE OR REPLACE FUNCTION fgedu_user_audit_trigger() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN IF TG_OP = ”INSERT” THEN INSERT INTO fgedu_user_audit(user_id, action, new_value) VALUES(NEW.id, ”INSERT”, row_to_json(NEW)::text); ELSIF TG_OP = ”UPDATE” THEN INSERT INTO fgedu_user_audit(user_id, action, old_value, new_value) VALUES(OLD.id, ”UPDATE”, row_to_json(OLD)::text, row_to_json(NEW)::text); ELSIF TG_OP = ”DELETE” THEN INSERT INTO fgedu_user_audit(user_id, action, old_value) VALUES(OLD.id, ”DELETE”, row_to_json(OLD)::text); END IF; RETURN NEW; END; $$;'”
CREATE FUNCTION
# 创建触发器
su – kingbase -c “psql -d fgedudb -c ‘CREATE TRIGGER fgedu_user_audit_trigger AFTER INSERT OR UPDATE OR DELETE ON fgedu_users FOR EACH ROW EXECUTE FUNCTION fgedu_user_audit_trigger();'”
CREATE TRIGGER
su – kingbase -c “psql -d fgedudb -c ‘CREATE TABLE fgedu_user_audit (id SERIAL PRIMARY KEY, user_id INTEGER, action VARCHAR(20), old_value TEXT, new_value TEXT, action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP);'”
CREATE TABLE
# 创建触发器函数
su – kingbase -c “psql -d fgedudb -c ‘CREATE OR REPLACE FUNCTION fgedu_user_audit_trigger() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN IF TG_OP = ”INSERT” THEN INSERT INTO fgedu_user_audit(user_id, action, new_value) VALUES(NEW.id, ”INSERT”, row_to_json(NEW)::text); ELSIF TG_OP = ”UPDATE” THEN INSERT INTO fgedu_user_audit(user_id, action, old_value, new_value) VALUES(OLD.id, ”UPDATE”, row_to_json(OLD)::text, row_to_json(NEW)::text); ELSIF TG_OP = ”DELETE” THEN INSERT INTO fgedu_user_audit(user_id, action, old_value) VALUES(OLD.id, ”DELETE”, row_to_json(OLD)::text); END IF; RETURN NEW; END; $$;'”
CREATE FUNCTION
# 创建触发器
su – kingbase -c “psql -d fgedudb -c ‘CREATE TRIGGER fgedu_user_audit_trigger AFTER INSERT OR UPDATE OR DELETE ON fgedu_users FOR EACH ROW EXECUTE FUNCTION fgedu_user_audit_trigger();'”
CREATE TRIGGER
4.2 触发器使用实战
测试触发器：，from DB视频:www.itpux.com
# 插入数据
su – kingbase -c “psql -d fgedudb -c ‘INSERT INTO fgedu_users(name, email) VALUES(”trigger_test”, ”trigger@fgedu.net.cn”);'”
INSERT 0 1
# 更新数据
su – kingbase -c “psql -d fgedudb -c ‘UPDATE fgedu_users SET name = ”trigger_test_updated” WHERE name = ”trigger_test”;'”
UPDATE 1
# 查看审计记录
su – kingbase -c “psql -d fgedudb -c ‘SELECT * FROM fgedu_user_audit;'”
id | user_id | action | old_value | new_value | action_time
—+———+——–+———–+———–+————
1 | 102 | INSERT | | {“id”:102,”name”:”trigger_test”,”email”:”trigger@fgedu.net.cn”,”age”:null,”created_at”:”2024-01-01T12:00:00″} | 2024-01-01 12:00:00
2 | 102 | UPDATE | {“id”:102,”name”:”trigger_test”,”email”:”trigger@fgedu.net.cn”,”age”:null,”created_at”:”2024-01-01T12:00:00″} | {“id”:102,”name”:”trigger_test_updated”,”email”:”trigger@fgedu.net.cn”,”age”:null,”created_at”:”2024-01-01T12:00:00″} | 2024-01-01 12:00:00
su – kingbase -c “psql -d fgedudb -c ‘INSERT INTO fgedu_users(name, email) VALUES(”trigger_test”, ”trigger@fgedu.net.cn”);'”
INSERT 0 1
# 更新数据
su – kingbase -c “psql -d fgedudb -c ‘UPDATE fgedu_users SET name = ”trigger_test_updated” WHERE name = ”trigger_test”;'”
UPDATE 1
# 查看审计记录
su – kingbase -c “psql -d fgedudb -c ‘SELECT * FROM fgedu_user_audit;'”
id | user_id | action | old_value | new_value | action_time
—+———+——–+———–+———–+————
1 | 102 | INSERT | | {“id”:102,”name”:”trigger_test”,”email”:”trigger@fgedu.net.cn”,”age”:null,”created_at”:”2024-01-01T12:00:00″} | 2024-01-01 12:00:00
2 | 102 | UPDATE | {“id”:102,”name”:”trigger_test”,”email”:”trigger@fgedu.net.cn”,”age”:null,”created_at”:”2024-01-01T12:00:00″} | {“id”:102,”name”:”trigger_test_updated”,”email”:”trigger@fgedu.net.cn”,”age”:null,”created_at”:”2024-01-01T12:00:00″} | 2024-01-01 12:00:00
4.3 触发器优化实战
优化触发器：
# 创建优化的触发器函数
su – kingbase -c “psql -d fgedudb -c ‘CREATE OR REPLACE FUNCTION fgedu_user_audit_trigger_optimized() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN IF TG_OP = ”INSERT” THEN INSERT INTO fgedu_user_audit(user_id, action, new_value) VALUES(NEW.id, ”INSERT”, row_to_json(NEW)::text); ELSIF TG_OP = ”UPDATE” THEN IF NEW.name <> OLD.name OR NEW.email <> OLD.email THEN INSERT INTO fgedu_user_audit(user_id, action, old_value, new_value) VALUES(OLD.id, ”UPDATE”, row_to_json(OLD)::text, row_to_json(NEW)::text); END IF; ELSIF TG_OP = ”DELETE” THEN INSERT INTO fgedu_user_audit(user_id, action, old_value) VALUES(OLD.id, ”DELETE”, row_to_json(OLD)::text); END IF; RETURN NEW; END; $$;'”
CREATE FUNCTION
# 更新触发器
su – kingbase -c “psql -d fgedudb -c ‘DROP TRIGGER IF EXISTS fgedu_user_audit_trigger ON fgedu_users; CREATE TRIGGER fgedu_user_audit_trigger AFTER INSERT OR UPDATE OR DELETE ON fgedu_users FOR EACH ROW EXECUTE FUNCTION fgedu_user_audit_trigger_optimized();'”
DROP TRIGGER
CREATE TRIGGER
su – kingbase -c “psql -d fgedudb -c ‘CREATE OR REPLACE FUNCTION fgedu_user_audit_trigger_optimized() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN IF TG_OP = ”INSERT” THEN INSERT INTO fgedu_user_audit(user_id, action, new_value) VALUES(NEW.id, ”INSERT”, row_to_json(NEW)::text); ELSIF TG_OP = ”UPDATE” THEN IF NEW.name <> OLD.name OR NEW.email <> OLD.email THEN INSERT INTO fgedu_user_audit(user_id, action, old_value, new_value) VALUES(OLD.id, ”UPDATE”, row_to_json(OLD)::text, row_to_json(NEW)::text); END IF; ELSIF TG_OP = ”DELETE” THEN INSERT INTO fgedu_user_audit(user_id, action, old_value) VALUES(OLD.id, ”DELETE”, row_to_json(OLD)::text); END IF; RETURN NEW; END; $$;'”
CREATE FUNCTION
# 更新触发器
su – kingbase -c “psql -d fgedudb -c ‘DROP TRIGGER IF EXISTS fgedu_user_audit_trigger ON fgedu_users; CREATE TRIGGER fgedu_user_audit_trigger AFTER INSERT OR UPDATE OR DELETE ON fgedu_users FOR EACH ROW EXECUTE FUNCTION fgedu_user_audit_trigger_optimized();'”
DROP TRIGGER
CREATE TRIGGER
Part05-风哥经验总结与分享
5.1 触发器使用最佳实践
- 合理使用触发器实现业务规则
- 保持触发器逻辑简洁明了
- 避免在触发器中执行复杂操作
- 添加适当的异常处理
- 定期审查和优化触发器
5.2 常见问题与解决方案
- 性能问题：优化触发器逻辑，避免复杂操作
- 死锁问题：避免在触发器中修改触发表
- 递归触发：添加适当的条件避免递归
- 权限问题：确保用户有适当的触发器执行权限
- 风哥提示：触发器是数据库开发的重要工具，需要根据实际需求合理使用
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
