openGauss 触发器零基础实战教程

一、先搞懂：触发器到底是什么？（通俗版）

1.1 核心定义

触发器（Trigger）是openGauss 中一种特殊的存储过程，它不需要手动调用，而是当数据表发生插入（INSERT）、修改（UPDATE）、删除（DELETE） 操作时，自动触发执行预先定义好的逻辑。

生活化类比：触发器就像医院的自动感应系统：

• 体温枪检测到体温≥38.5℃（触发事件）→ 自动触发警报（执行逻辑）

• 患者刷医保卡挂号（触发事件）→ 自动扣减号源、生成缴费单（执行逻辑）

• 药品库存低于 10 盒（触发事件）→ 自动发送补货提醒（执行逻辑）

1.2 触发器 vs 存储过程（衔接旧知识，学生易混点）

1.3 什么时候必须用触发器？（核心使用场景，结合医院业务）

学生最常问：“我在 Java/Python 代码里写逻辑不行吗？为什么要用触发器？”
答案：当需要保证所有数据库操作都必须执行某段逻辑时，触发器是最可靠的方案 —— 无论哪个应用、哪个用户操作数据库，只要触发了事件，逻辑就会自动执行，不会遗漏。

必用场景（医院真实业务）

1. 数据合法性强制校验
   场景：挂号费不能为负数、医生薪资不能低于当地最低工资、患者年龄不能超过 150 岁。
   优势：在数据写入数据库前就拦截非法数据，比应用层校验更可靠（避免有人直接通过数据库工具修改数据）。

2. 关联表级联更新 / 删除
   场景：删除科室表中的 “心内科”，自动删除医生表中所有心内科医生；更新科室编码，自动同步更新医生表的科室编码。
   优势：保证数据一致性，避免出现 “医生属于不存在的科室” 这种脏数据。

3. 数据自动备份与归档
   场景：新增挂号记录时，自动同步一份到挂号备份表；删除患者信息时，自动将数据归档到历史表。
   优势：无需手动写备份逻辑，实时自动备份，数据更安全。

4. 操作日志自动记录
   场景：任何人修改医生薪资、删除挂号记录时，自动记录操作人、操作时间、修改前后的数据。
   优势：实现数据操作可追溯，方便排查问题和审计。

5. 统计数据自动更新
   场景：新增挂号记录时，自动更新医生表的 “接诊人次” 字段；新增药品出库记录时，自动扣减药品库存。
   优势：统计数据实时更新，无需每次查询都重新计算，提升查询效率。

1.4 触发器的优缺点（客观全面）

二、前置准备：触发器核心语法与关键字

2.1 openGauss 触发器创建语法（分两步，学生必记）

触发器的创建必须分两步：先创建触发器函数（写具体执行逻辑），再创建触发器（绑定表和触发事件）。

第一步：创建触发器函数

CREATE OR REPLACE FUNCTION 触发器函数名()
RETURNS TRIGGER  -- 触发器函数必须返回TRIGGER类型
AS $$
DECLARE
    -- 变量声明区（可选）
    变量名 数据类型;
BEGIN
    -- 触发器执行的核心逻辑（SQL语句、判断、循环等）
    -- 用NEW访问新数据，OLD访问旧数据
    ...
    RETURN NEW;  -- INSERT/UPDATE触发器返回NEW，DELETE触发器返回OLD
END;
$$ LANGUAGE plpgsql;  -- 固定写法，指定语言为plpgsql

第二步：创建触发器

CREATE TRIGGER 触发器名
BEFORE | AFTER  -- 触发时机：事件执行前/后
{INSERT | UPDATE | DELETE}  -- 触发事件：可同时指定多个，用逗号分隔
ON 表名
FOR EACH ROW  -- 行级触发器：每影响一行就执行一次（openGauss仅支持行级）
EXECUTE FUNCTION 触发器函数名();  -- 调用触发器函数

2.2 核心关键字：NEW 和 OLD（学生最难懂的点，通俗讲）

NEW 和 OLD 是触发器的内置变量，用来传递触发事件前后的数据，不同触发事件的可用情况不同：

⚠️ 重要提醒：OLD 关键字获取的字段是只读的，不能修改；NEW 关键字在 BEFORE 触发器中可以修改，在 AFTER 触发器中不能修改。

2.3 触发时机：BEFORE vs AFTER（怎么选？）

三、实战案例 1：INSERT 触发器（新增数据时触发）

需求：新增挂号记录时，自动完成 2 件事

1. 校验挂号费不能为负数（BEFORE INSERT，非法数据直接拦截）

2. 自动将挂号记录同步到备份表his_reg_backup（AFTER INSERT，数据备份）

步骤 1：先创建挂号备份表

-- 创建挂号备份表，结构和挂号表完全一致
CREATE TABLE his_reg_backup (
    reg_id INT PRIMARY KEY,
    pat_name VARCHAR(20) NOT NULL,
    emp_id INT,
    reg_fee DECIMAL(5,2),
    backup_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- 新增备份时间字段
);

步骤 2：创建 BEFORE INSERT 触发器函数（数据校验）

CREATE OR REPLACE FUNCTION func_check_reg_fee()
RETURNS TRIGGER
AS $$
BEGIN
    -- 校验挂号费不能为负数
    IF NEW.reg_fee < 0 THEN
        -- 抛出异常，终止插入操作
        RAISE EXCEPTION '挂号费不能为负数！当前输入的挂号费：%', NEW.reg_fee;
    END IF;
    -- 校验通过，返回NEW，继续执行插入
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤 3：创建 BEFORE INSERT 触发器

CREATE TRIGGER trg_before_insert_reg
BEFORE INSERT ON his_reg
FOR EACH ROW
EXECUTE FUNCTION func_check_reg_fee();

步骤 4：创建 AFTER INSERT 触发器函数（自动备份）

CREATE OR REPLACE FUNCTION func_backup_reg()
RETURNS TRIGGER
AS $$
BEGIN
    -- 将新插入的挂号记录同步到备份表
    INSERT INTO his_reg_backup(reg_id, pat_name, emp_id, reg_fee)
    VALUES(NEW.reg_id, NEW.pat_name, NEW.emp_id, NEW.reg_fee);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤 5：创建 AFTER INSERT 触发器

CREATE TRIGGER trg_after_insert_reg
AFTER INSERT ON his_reg
FOR EACH ROW
EXECUTE FUNCTION func_backup_reg();

测试触发器

测试 1：插入合法数据（挂号费 30 元）

INSERT INTO his_reg(reg_id, pat_name, emp_id, reg_fee)
VALUES(6, '患者己', 3, 30.00);

✅ 执行结果：插入成功，同时his_reg_backup表中自动新增了这条记录。

测试 2：插入非法数据（挂号费 - 10 元）

INSERT INTO his_reg(reg_id, pat_name, emp_id, reg_fee)
VALUES(7, '患者庚', 4, -10.00);

❌ 执行结果：抛出异常ERROR: 挂号费不能为负数！当前输入的挂号费：-10.00，插入被拦截。

四、实战案例 2：UPDATE 触发器（修改数据时触发）

需求：更新科室表的科室编码时，自动同步更新医生表的科室编码（级联更新）

步骤 1：创建 UPDATE 触发器函数

CREATE OR REPLACE FUNCTION func_update_dpt_code()
RETURNS TRIGGER
AS $$
BEGIN
    -- 当科室编码被修改时，同步更新医生表中对应的科室编码
    UPDATE his_emp
    SET dpt_code = NEW.dpt_code
    WHERE dpt_code = OLD.dpt_code;
    RAISE INFO '科室编码从%更新为%，已同步更新医生表', OLD.dpt_code, NEW.dpt_code;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤 2：创建 AFTER UPDATE 触发器

CREATE TRIGGER trg_after_update_dpt
AFTER UPDATE OF dpt_code ON his_dpt -- 仅当dpt_code字段被修改时触发
FOR EACH ROW
EXECUTE PROCEDURE func_update_dpt_code();

测试触发器

-- 将心内科的编码从NEI01修改为NEI001
UPDATE his_dpt SET dpt_code = 'NEI001' WHERE dpt_name = '心内科';

✅ 执行结果：

1. 科室表his_dpt中的心内科编码更新为NEI001

2. 医生表his_emp中张医生（1 号）、王医生（3 号）的dpt_code自动更新为NEI001

3. 控制台输出：INFO: 科室编码从NEI01更新为NEI001，已同步更新医生表

五、实战案例 3：DELETE 触发器（删除数据时触发）

需求：删除医生时，自动删除该医生的所有挂号记录（级联删除）

步骤 1：创建 DELETE 触发器函数

CREATE OR REPLACE FUNCTION func_delete_emp_reg()
RETURNS TRIGGER
AS $$
BEGIN
    -- 删除医生时，同步删除该医生的所有挂号记录
    DELETE FROM his_reg WHERE emp_id = OLD.emp_id;
    RAISE INFO '已删除医生%的所有挂号记录，共删除%条', OLD.emp_name, FOUND;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

步骤 2：创建 AFTER DELETE 触发器

CREATE TRIGGER trg_after_delete_emp
AFTER DELETE ON his_emp
FOR EACH ROW
EXECUTE FUNCTION func_delete_emp_reg();

测试触发器

-- 删除普外科的李医生（2号）
DELETE FROM his_emp WHERE emp_id = 2;

✅ 执行结果：

1. 医生表his_emp中 2 号李医生被删除

2. 挂号表his_reg中患者丙（3 号挂号单，接诊医生 2 号）被自动删除

3. 控制台输出：INFO: 已删除医生李医生的所有挂号记录，共删除1条

六、触发器的管理：查看、删除、禁用、启用

6.1 查看触发器

1. gsql 客户端快捷命令

   -- 查看当前库所有触发器
   \dft
   -- 查看指定表的所有触发器
   \d 表名
   

2. 系统表查询（通用）

   -- 查看触发器名称、绑定的表、触发事件、时机
   SELECT tgname 触发器名, relname 表名, 
          CASE tgtype 
              WHEN 5 THEN 'INSERT'
              WHEN 7 THEN 'UPDATE'
              WHEN 9 THEN 'DELETE'
          END 触发事件,
          CASE WHEN tgconstraint = 0 THEN 'BEFORE' ELSE 'AFTER' END 触发时机
   FROM pg_trigger
   JOIN pg_class ON pg_trigger.tgrelid = pg_class.oid
   WHERE tgisinternal = false;  -- 排除系统内部触发器
   

6.2 删除触发器

语法：

DROP TRIGGER [IF EXISTS] 触发器名 ON 表名;

案例：

-- 删除挂号表的插入前触发器
DROP TRIGGER IF EXISTS trg_before_insert_reg ON his_reg;

6.3 禁用和启用触发器

当需要临时关闭触发器（比如批量导入数据时），可以禁用触发器，用完再启用：

-- 禁用单个触发器
ALTER TABLE 表名 DISABLE TRIGGER 触发器名;
-- 启用单个触发器
ALTER TABLE 表名 ENABLE TRIGGER 触发器名;

-- 禁用表上所有触发器
ALTER TABLE 表名 DISABLE TRIGGER ALL;
-- 启用表上所有触发器
ALTER TABLE 表名 ENABLE TRIGGER ALL;

案例：

-- 临时禁用医生表的删除触发器
ALTER TABLE his_emp DISABLE TRIGGER trg_after_delete_emp;
-- 批量导入医生数据后，重新启用
ALTER TABLE his_emp ENABLE TRIGGER trg_after_delete_emp;

七、综合实战案例（全知识点串联）

需求：医院药品库存自动管理触发器

实现以下逻辑：

1. BEFORE INSERT：新增药品时，校验药品单价不能为负，库存不能小于 0

2. AFTER UPDATE：药品库存低于 5 盒时，自动打印补货提醒

3. AFTER DELETE：删除药品时，自动记录到药品删除日志表

步骤 1：创建药品删除日志表

CREATE TABLE his_drug_log (
    log_id SERIAL PRIMARY KEY,
    drug_id INT,
    drug_name VARCHAR(30),
    delete_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

步骤 2：创建 BEFORE INSERT 触发器函数（数据校验）

CREATE OR REPLACE FUNCTION func_check_drug()
RETURNS TRIGGER
AS $$
BEGIN
    IF NEW.drug_price < 0 THEN
        RAISE EXCEPTION '药品单价不能为负数！';
    END IF;
    IF NEW.drug_num < 0 THEN
        RAISE EXCEPTION '药品库存不能为负数！';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤 3：创建 BEFORE INSERT 触发器

CREATE TRIGGER trg_before_insert_drug
BEFORE INSERT ON his_drug
FOR EACH ROW
EXECUTE FUNCTION func_check_drug();

步骤 4：创建 AFTER UPDATE 触发器函数（库存预警）

CREATE OR REPLACE FUNCTION func_drug_stock_alert()
RETURNS TRIGGER
AS $$
BEGIN
    IF NEW.drug_num < 5 THEN
        RAISE WARNING '药品【%】库存不足！当前库存：%盒，请及时补货', NEW.drug_name, NEW.drug_num;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤 5：创建 AFTER UPDATE 触发器

CREATE TRIGGER trg_after_update_drug
AFTER UPDATE OF drug_num ON his_drug
FOR EACH ROW
EXECUTE FUNCTION func_drug_stock_alert();

步骤 6：创建 AFTER DELETE 触发器函数（日志记录）

CREATE OR REPLACE FUNCTION func_log_delete_drug()
RETURNS TRIGGER
AS $$
BEGIN
    INSERT INTO his_drug_log(drug_id, drug_name)
    VALUES(OLD.drug_id, OLD.drug_name);
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

步骤 7：创建 AFTER DELETE 触发器

CREATE TRIGGER trg_after_delete_drug
AFTER DELETE ON his_drug
FOR EACH ROW
EXECUTE FUNCTION func_log_delete_drug();

测试触发器

-- 测试1：新增药品（合法数据）
INSERT INTO his_drug(drug_id, drug_name, drug_price, drug_manu, drug_num)
VALUES(6, '感冒清热颗粒', 15.80, '同仁堂', 10);

-- 测试2：更新药品库存（触发库存预警）
UPDATE his_drug SET drug_num = 3 WHERE drug_id = 6;
-- 输出：WARNING: 药品【感冒清热颗粒】库存不足！当前库存：3盒，请及时补货

-- 测试3：删除药品（自动记录日志）
DELETE FROM his_drug WHERE drug_id = 6;
-- 查询日志表，已自动记录删除信息
SELECT * FROM his_drug_log;

八、学生必记核心总结

1. 触发器本质：自动执行的特殊存储过程，绑定表的 INSERT/UPDATE/DELETE 事件，事件发生时自动触发。

2. 核心使用场景：数据校验、级联更新 / 删除、自动备份、日志记录、库存预警。

3. NEW/OLD 关键字：INSERT 用 NEW，DELETE 用 OLD，UPDATE 两者都用；OLD 只读，BEFORE 触发器中可修改 NEW。

4. 触发时机选择：BEFORE 用于数据校验，AFTER 用于备份、日志、级联操作。

5. 创建步骤：先创建触发器函数（返回 TRIGGER），再创建触发器绑定表和事件。

6. 注意事项：不要过度使用触发器，避免数据库性能下降；复杂逻辑优先用存储过程，触发器只做简单的自动操作。

九、常见坑点提醒（学生易踩）

1. ❌ 触发器函数忘记写RETURN NEW/OLD：会导致触发器执行失败。

2. ❌ DELETE 触发器中使用 NEW：DELETE 没有新数据，会报错。

3. ❌ AFTER 触发器中修改 NEW：AFTER 触发器中 NEW 是只读的，修改无效。

4. ❌ 触发器循环触发：比如 A 表触发器修改 B 表，B 表触发器又修改 A 表，会导致死循环。

5. ❌ 批量操作时性能问题：批量插入 1000 条数据，行级触发器会执行 1000 次，影响性能。

（注：）

openGauss 触发器函数多表操作详解

一、完全可以！这是触发器的核心优势之一

触发器函数本质是标准的 PL/pgSQL 存储过程，支持在一个函数内对任意多个数据表执行增删改查操作，且所有操作会在同一个事务中执行 —— 要么全部成功，要么全部失败，完美保证多表数据一致性。

生活化类比：就像医院删除一个科室（触发事件），会自动触发连锁反应：

1. 该科室所有医生自动离职（操作医生表）

2. 这些医生的所有未完成挂号自动作废（操作挂号表）

3. 科室的药品库存自动转移到药房总库（操作药品表）
   这一整套连锁操作，都可以写在同一个触发器函数里自动执行。

二、核心原理

触发器函数没有 “只能操作绑定表” 的限制，只要数据库用户有对应表的操作权限，就可以在函数内对任意表执行 SQL 语句。

• 触发器绑定的表：只是触发事件的来源（比如删除科室表触发）

• 触发器函数内操作的表：可以是任意有权限的表（科室表、医生表、挂号表都可以）

三、实战案例：一个触发器函数操作 3 个表

需求：删除科室时，自动完成 3 件事（全在一个函数里）

1. 删除该科室的所有医生（操作his_emp）

2. 删除这些医生的所有挂号记录（操作his_reg）

3. 记录删除日志到his_dpt_log表（操作日志表）

步骤 1：先创建科室删除日志表

CREATE TABLE his_dpt_log (
    log_id SERIAL PRIMARY KEY,
    dpt_code VARCHAR(10),
    dpt_name VARCHAR(20),
    delete_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    delete_emp_count INT,  -- 删除的医生数量
    delete_reg_count INT   -- 删除的挂号数量
);

步骤 2：创建触发器函数（同时操作 3 个表）

CREATE OR REPLACE FUNCTION func_delete_dpt_cascade()
RETURNS TRIGGER
AS $$
DECLARE
    v_emp_count INT;  -- 记录删除的医生数量
    v_reg_count INT;  -- 记录删除的挂号数量
BEGIN
    -- 1. 先删除该科室医生的所有挂号记录（操作his_reg）
    DELETE FROM his_reg 
    WHERE emp_id IN (SELECT emp_id FROM his_emp WHERE dpt_code = OLD.dpt_code);
    GET DIAGNOSTICS v_reg_count = ROW_COUNT;  -- 获取删除的行数

    -- 2. 再删除该科室的所有医生（操作his_emp）
    DELETE FROM his_emp WHERE dpt_code = OLD.dpt_code;
    GET DIAGNOSTICS v_emp_count = ROW_COUNT;

    -- 3. 最后记录删除日志（操作his_dpt_log）
    INSERT INTO his_dpt_log(dpt_code, dpt_name, delete_emp_count, delete_reg_count)
    VALUES(OLD.dpt_code, OLD.dpt_name, v_emp_count, v_reg_count);

    -- 打印执行结果
    RAISE INFO '删除科室【%】成功：删除医生%人，删除挂号%条，已记录日志', 
               OLD.dpt_name, v_emp_count, v_reg_count;

    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

步骤 3：创建触发器（绑定科室表的 DELETE 事件）

CREATE TRIGGER trg_after_delete_dpt
AFTER DELETE ON his_dpt
FOR EACH ROW
EXECUTE PROCEDURE func_delete_dpt_cascade();  -- openGauss必须用PROCEDURE

测试触发器

-- 删除儿科（PED01）
DELETE FROM his_dpt WHERE dpt_code = 'PED01';

✅ 执行结果（自动完成 3 个表的操作）：

1. 科室表hisdpt中儿科被删除

2. 医生表his_emp中刘医生（5 号）被自动删除

3. 挂号表his_reg中刘医生的挂号记录（如果有）被自动删除

4. 日志表his_dpt_log中自动生成一条删除记录

5. 控制台输出：INFO: 删除科室【儿科】成功：删除医生1人，删除挂号0条，已记录日志

四、常见多表操作场景（医院业务举例）

五、重要注意事项（学生必记，避免踩坑）

1. ❌ 绝对避免循环触发

错误示例：A 表触发器修改 B 表，B 表触发器又修改 A 表，会导致无限循环，数据库崩溃。

-- 错误：A表更新触发B表更新，B表更新又触发A表更新
CREATE TRIGGER trg_a AFTER UPDATE ON a FOR EACH ROW EXECUTE PROCEDURE func_update_b();
CREATE TRIGGER trg_b AFTER UPDATE ON b FOR EACH ROW EXECUTE PROCEDURE func_update_a();

2. ✅ 保证事务原子性

触发器函数内的所有操作都在同一个事务中，只要有一个操作失败，所有操作都会回滚。
比如上面的案例：如果删除挂号记录失败，那么删除医生和记录日志的操作也会全部撤销，不会出现部分成功的情况。

3. ⚠️ 注意性能问题

• 一个触发器函数操作的表越多，执行时间越长

• 批量操作（比如删除 100 个科室）时，行级触发器会执行 100 次，性能影响较大

• 建议：复杂的多表批量操作，优先用存储过程手动调用，不要用触发器

4. 🔒 权限问题

触发器函数的执行权限是触发器创建者的权限，不是调用者的权限。
比如：普通用户只能删除科室，但触发器函数可以自动删除医生和挂号记录（只要创建者有这些权限）。

六、一句话总结

触发器函数可以操作任意多个表，是实现多表级联操作、数据一致性保证的最佳工具，但要避免循环触发和过度使用，复杂逻辑优先用存储过程。