阅读量:0
openGauss 是一款开源关系型数据库管理系统，广泛应用于企业级应用中。随着数据量的增长和业务逻辑的复杂化，数据库管理和操作的自动化需求越来越高。触发器（Triggers）作为数据库中重要的编程工具，能够极大地简化复杂操作，提高系统的性能和安全性。openGauss触发器会在指定的数据库事件发生时自动执行函数。本文将详细介绍 openGauss 的触发器，并提供具体的代码和案例，以帮助读者更好地理解和应用这些工具。
目录
一、什么是触发器
触发器是一种特殊类型的存储过程，它会在特定事件（如插入、更新、删除）发生时自动执行。触发器能够自动响应数据库表中的变化，进行数据验证、日志记录等操作。使用触发器可以确保数据的完整性、一致性，并实现复杂的业务逻辑。
触发器的特点包括：
- 自动执行：触发器在指定事件发生时自动执行，无需显式调用。
- 灵活性：可以根据具体业务需求，灵活定义触发器的执行逻辑。
- 实时性：触发器在事件发生时立即执行，保证数据的实时性。
二、创建和使用触发器
在 openGauss 中，创建触发器需要使用 CREATE TRIGGER 语句。触发器通常需要配合触发器函数（存储过程）一起使用。下面是多个触发器的例子，演示如何创建和使用触发器。
1. 创建日志表
-- 创建日志表
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) );
2. 创建触发器函数和触发器
-- 创建插入触发器函数
-- 创建插入触发器函数 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; $$;
-- 创建更新触发器函数 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; $$;
3. 创建删除触发器函数
-- 创建删除触发器函数 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; $$;DROP TRIGGER trigger_name ON table_name [ CASCADE | RESTRICT ]; 4. 创建触发器
-- 创建触发器 CREATE TRIGGER trigger_insert_employee AFTER INSERT ON employees FOR EACH ROW EXECUTE FUNCTION log_insert_employee();
CREATE TRIGGER trigger_update_employee AFTER UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION log_update_employee();
CREATE TRIGGER trigger_delete_employee AFTER DELETE ON employees FOR EACH ROW EXECUTE FUNCTION log_delete_employee();
CREATE TRIGGER trigger_name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] } ON table_name [ FOR [ EACH ] { ROW | STATEMENT } ] [ WHEN ( condition ) ] EXECUTE PROCEDURE function_name ( arguments ); 5.修改触发器
ALTER TRIGGER trigger_name ON table_name RENAME TO new_trigger_name; 三、验证触发器
通过插入、更新和删除操作来验证触发器的功能，确保日志表记录了相应的变更。
1. 插入员工数据以触发触发器
INSERT INTO employees (id, name, salary, department) VALUES (1, 'John Doe', 50000, 'Engineering');
2. 更新员工数据以触发触发器
UPDATE employees SET name = 'John Doe', salary = 55000, department = 'Marketing' WHERE id = 1;
3. 删除员工数据以触发触发器
DELETE FROM employees WHERE id = 1;
4. 查询日志表
SELECT * FROM employee_changes;
四、 触发器的高级应用
触发器不仅可以用于基本的数据变更日志记录，还可以用于更复杂的业务逻辑处理。以下是一些触发器的高级应用场景：
1. 数据完整性维护
触发器可以在数据插入、更新或删除时自动检查和维护数据的完整性。例如，可以在员工表中添加触发器，确保同一部门中的员工薪资总和不超过某个限制。
-- 创建触发器函数
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; $$;
-- 创建触发器
CREATE TRIGGER trigger_check_salary_limit BEFORE INSERT OR UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION check_salary_limit();
2. 审计和日志记录
触发器可以记录数据的变化历史，便于追踪和审计。例如，可以在员工表中添加触发器，记录每次更新操作的详细信息，包括操作人、操作时间和更新前后的数据。
-- 创建审计日志表
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; $$;
-- 创建触发器
CREATE TRIGGER trigger_audit_log AFTER INSERT OR UPDATE OR DELETE ON employees FOR EACH ROW EXECUTE FUNCTION log_audit();
3. 自动计算和更新
触发器可以在数据发生变化时自动计算和更新相关联的数据，保持数据的一致性。例如，可以在订单表中添加触发器，当订单状态变为“已发货”时，自动更新库存表。
-- 创建订单表
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();
五、参数说明
trigger_name
触发器名称。
BEFORE
触发器函数是在触发事件发生前执行。
AFTER
触发器函数是在触发事件发生后执行。
INSTEAD OF
触发器函数直接替代触发事件。
event
启动触发器的事件，取值范围包括：INSERT、UPDATE、DELETE或TRUNCATE，也可以通过OR同时指定多个触发事件。
table_name
触发器对应的表名称。
FOR EACH ROW | FOR EACH STATEMENT
触发器的触发频率。
- FOR EACH ROW是指该触发器是受触发事件影响的每一行触发一次。
- FOR EACH STATEMENT是指该触发器是每个SQL语句只触发一次。
未指定时默认值为FOR EACH STATEMENT。约束触发器只能指定为FOR EACH ROW。
function_name
用户定义的函数，必须声明为不带参数并返回类型为触发器，在触发器触发时执行。
arguments
执行触发器时要提供给函数的可选的以逗号分隔的参数列表。
new_trigger_name
修改后的新触发器名称。
六、总结
触发器是 openGauss 数据库中的重要工具，能够帮助开发者简化复杂的数据库操作，实现自动化管理。在实际应用中，通过合理地使用触发器，可以提高数据库系统的效率和可靠性。本文详细介绍了触发器的基本概念、创建方法、应用场景，并提供了具体的代码和案例，帮助读者更好地理解和应用这些工具。希望本文能够对您在数据库开发和管理中有所帮助。
