openGauss触发器详解

举报
yd_286645194 发表于 2024/08/06 15:36:38 2024/08/06
【摘要】 openGauss 是一款开源关系型数据库管理系统，广泛应用于企业级应用中。随着数据量的增长和业务逻辑的复杂化，数据库管理和操作的自动化需求越来越高。触发器（Triggers）作为数据库中重要的编程工具，能够极大地简化复杂操作，提高系统的性能和安全性。openGauss触发器会在指定的数据库事件发生时自动执行函数。本文将详细介绍 openGauss 的触发器，并提供具体的代码和案例，以帮助读...

openGauss 是一款开源关系型数据库管理系统，广泛应用于企业级应用中。随着数据量的增长和业务逻辑的复杂化，数据库管理和操作的自动化需求越来越高。触发器（Triggers）作为数据库中重要的编程工具，能够极大地简化复杂操作，提高系统的性能和安全性。openGauss触发器会在指定的数据库事件发生时自动执行函数。本文将详细介绍 openGauss 的触发器，并提供具体的代码和案例，以帮助读者更好地理解和应用这些工具。 

一、触发器

1. 什么是触发器

触发器是一种特殊类型的存储过程，它会在特定事件（如插入、更新、删除）发生时自动执行。触发器能够自动响应数据库表中的变化，进行数据验证、日志记录等操作。使用触发器可以确保数据的完整性、一致性，并实现复杂的业务逻辑。

触发器的特点包括：

  • 自动执行：触发器在指定事件发生时自动执行，无需显式调用。
  • 灵活性：可以根据具体业务需求，灵活定义触发器的执行逻辑。
  • 实时性：触发器在事件发生时立即执行，保证数据的实时性。

2. 创建和使用触发器

在 openGauss 中，创建触发器需要使用 CREATE TRIGGER 语句。触发器通常需要配合触发器函数（存储过程）一起使用。下面是多个触发器的例子，演示如何创建和使用触发器。

创建日志表

-- 创建日志表


CREATE TABLE employee_changes (

change_id SERIAL PRIMARY KEY,

emp_id INT,

change_type VARCHAR(10),

change_time TIMESTAMP,

old_name VARCHAR(100),

new_name VARCHAR(100),

old_salary NUMERIC(15, 2),

new_salary NUMERIC(15, 2),

old_department VARCHAR(100),

new_department VARCHAR(100)

);复制

cke_56713.png

创建触发器函数和触发器

-- 创建插入触发器函数


CREATE OR REPLACE FUNCTION log_insert_employee()

RETURNS TRIGGER

LANGUAGE plpgsql

AS $$

BEGIN

INSERT INTO employee_changes (emp_id, change_type, change_time, new_name, new_salary, new_department)

VALUES (NEW.id, 'INSERT', CURRENT_TIMESTAMP, NEW.name, NEW.salary, NEW.department);

RETURN NEW;

END;

$$;复制

cke_56714.png

-- 创建更新触发器函数


CREATE OR REPLACE FUNCTION log_update_employee()

RETURNS TRIGGER

LANGUAGE plpgsql

AS $$

BEGIN

INSERT INTO employee_changes (emp_id, change_type, change_time, old_name, new_name, old_salary, new_salary, old_department, new_department)

VALUES (OLD.id, 'UPDATE', CURRENT_TIMESTAMP, OLD.name, NEW.name, OLD.salary, NEW.salary, OLD.department, NEW.department);

RETURN NEW;

END;

$$;复制

cke_56715.png

-- 创建删除触发器函数


CREATE OR REPLACE FUNCTION log_delete_employee()

RETURNS TRIGGER

LANGUAGE plpgsql

AS $$

BEGIN

INSERT INTO employee_changes (emp_id, change_type, change_time, old_name, old_salary, old_department)

VALUES (OLD.id, 'DELETE', CURRENT_TIMESTAMP, OLD.name, OLD.salary, OLD.department);

RETURN OLD;

END;

$$;复制

-- 创建触发器


CREATE TRIGGER trigger_insert_employee

AFTER INSERT ON employees

FOR EACH ROW

EXECUTE FUNCTION log_insert_employee();复制

cke_56716.png


CREATE TRIGGER trigger_update_employee

AFTER UPDATE ON employees

FOR EACH ROW

EXECUTE FUNCTION log_update_employee();复制

cke_56717.png


CREATE TRIGGER trigger_delete_employee

AFTER DELETE ON employees

FOR EACH ROW

EXECUTE FUNCTION log_delete_employee();复制

cke_56718.png

3. 验证触发器

通过插入、更新和删除操作来验证触发器的功能，确保日志表记录了相应的变更。

-- 插入员工数据以触发触发器


INSERT INTO employees (id, name, salary, department)

VALUES (1, 'John Doe', 50000, 'Engineering');复制

cke_56719.png

-- 更新员工数据以触发触发器


UPDATE employees

SET name = 'John Doe', salary = 55000, department = 'Marketing'

WHERE id = 1;复制

cke_56720.png

-- 删除员工数据以触发触发器


DELETE FROM employees

WHERE id = 1;复制

cke_56721.png

-- 查询日志表



SELECT * FROM employee_changes;复制

cke_56722.png

4. 触发器的高级应用

触发器不仅可以用于基本的数据变更日志记录，还可以用于更复杂的业务逻辑处理。以下是一些触发器的高级应用场景：

数据完整性维护

触发器可以在数据插入、更新或删除时自动检查和维护数据的完整性。例如，可以在员工表中添加触发器，确保同一部门中的员工薪资总和不超过某个限制。

-- 创建触发器函数


CREATE OR REPLACE FUNCTION check_salary_limit()

RETURNS TRIGGER

LANGUAGE plpgsql

AS $$

DECLARE

total_salary NUMERIC;

BEGIN

SELECT SUM(salary) INTO total_salary

FROM employees

WHERE department = NEW.department;

IF total_salary + NEW.salary > 1000000 THEN

RAISE EXCEPTION 'Total salary in department % exceeds limit', NEW.department;

END IF;

RETURN NEW;

END;

$$;复制

cke_56723.png

-- 创建触发器


CREATE TRIGGER trigger_check_salary_limit

BEFORE INSERT OR UPDATE ON employees

FOR EACH ROW

EXECUTE FUNCTION check_salary_limit();复制

cke_56724.png

审计和日志记录

触发器可以记录数据的变化历史，便于追踪和审计。例如，可以在员工表中添加触发器，记录每次更新操作的详细信息，包括操作人、操作时间和更新前后的数据。

-- 创建审计日志表


CREATE TABLE audit_log (

log_id SERIAL PRIMARY KEY,

emp_id INT,

operation VARCHAR(10),

operation_time TIMESTAMP,

operator VARCHAR(100),

old_data JSON,

new_data JSON

);复制

-- 创建触发器函数


CREATE OR REPLACE FUNCTION log_audit()

RETURNS TRIGGER

LANGUAGE plpgsql

AS $$

BEGIN

INSERT INTO audit_log (emp_id, operation, operation_time, operator, old_data, new_data)

VALUES (

NEW.id,

TG_OP,

CURRENT_TIMESTAMP,

current_user,

ROW_TO_JSON(OLD),

ROW_TO_JSON(NEW)

);

RETURN NEW;

END;

$$;复制

cke_56725.png

-- 创建触发器


CREATE TRIGGER trigger_audit_log

AFTER INSERT OR UPDATE OR DELETE ON employees

FOR EACH ROW

EXECUTE FUNCTION log_audit();复制

cke_56726.png

自动计算和更新

触发器可以在数据发生变化时自动计算和更新相关联的数据，保持数据的一致性。例如，可以在订单表中添加触发器，当订单状态变为“已发货”时，自动更新库存表。

-- 创建订单表


CREATE TABLE orders (

order_id INT PRIMARY KEY,

product_id INT,

quantity INT,

status VARCHAR(20)

);复制

-- 创建库存表


CREATE TABLE inventory (

product_id INT PRIMARY KEY,

stock INT

);复制

-- 创建触发器函数


CREATE OR REPLACE FUNCTION update_inventory()

RETURNS TRIGGER

LANGUAGE plpgsql

AS $$

BEGIN

IF NEW.status = 'Shipped' THEN

UPDATE inventory

SET stock = stock - NEW.quantity

WHERE product_id = NEW.product_id;

END IF;

RETURN NEW;

END;

$$;复制

-- 创建触发器


CREATE TRIGGER trigger_update_inventory

AFTER UPDATE ON orders

FOR EACH ROW

WHEN (NEW.status = 'Shipped')

EXECUTE FUNCTION update_inventory();复制


二、总结

存储过程和触发器是 openGauss 数据库中的重要工具，能够帮助开发者简化复杂的数据库操作，实现自动化管理。在实际应用中，通过合理地使用存储过程和触发器，可以提高数据库系统的效率和可靠性。本文详细介绍了存储过程和触发器的基本概念、创建方法、应用场景，并提供了具体的代码和案例，帮助读者更好地理解和应用这些工具。希望本文能够对您在数据库开发和管理中有所帮助。

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入