触发器

触发器

• 触发器有行触发器、事件触发器
• 触发器是区分数据库的

案例一：行触发器

• 触发器要实现的效果是，删除student表的记录时，score中的关联记录会一并删除
• 当要删除的表有外键时，不能直接删除，需要先删除外键约束

查询score表的外键名

SELECT conname FROM pg_constraint 
WHERE conrelid = 'score'::regclass AND contype = 'f';

删除score表的外键约束

• 查询到的外键约束名为score_student_no_fkey

ALTER TABLE score DROP CONSTRAINT score_student_no_fkey;

创建student表

CREATE TABLE student (
  id integer PRIMARY KEY,
  name varchar(32) NOT NULL,
  score integer CHECK (score >= 0 AND score <= 100),
  student_no integer UNIQUE NOT NULL
);

插入测试数据

INSERT INTO student (id, name, score, student_no)
VALUES
  (1, '张三', 85, 2026001),
  (2, '李四', 92, 2026002),
  (3, '王五', 78, 2026003);

创建score表

CREATE TABLE score (
  id integer PRIMARY KEY, 
  student_no integer NOT NULL,
  subject varchar(32) NOT NULL,
  score integer CHECK (score >= 0 AND score <= 100),
  -- 外键约束
  FOREIGN KEY (student_no) REFERENCES student(student_no) 
);

插入测试数据

INSERT INTO score (id, student_no, subject, score)
VALUES
  (1, 2026001, '数学', 88),
  (2, 2026001, '语文', 82),
  (3, 2026002, '数学', 95),
  (4, 2026002, '英语', 89),
  (5, 2026003, '语文', 75),
  (6, 2026003, '英语', 81);

创建触发器函数

• 触发器函数是触发器真正要执行的逻辑
• begin、end、as、$$是固定写法
• $$是边界符号，用来定义函数体的范围，避免使用使用引号导致的结构混乱。 $test\$是自定义的边界符号。
• returns trigger、return old是固定写法。操作是insert、update前，写new；操作是delete、update后，写old
• begin和end间是触发器要执行的动作，前一个student_no是score的，后面的是student的

CREATE OR REPLACE FUNCTION student_delete_trigger_fun()
returns trigger as $$
begin
    delete from score where student_no = old.student_no;
    return old;
end;
$$
language plpgsql;

创建触发器

• 语法

CREATE [ CONSTRAINT ] TRIGGER name 
{ BEFORE | AFTER | INSTEAD OF } { event [ OR ... ]}
ON table_name
[ FROM referenced_table_name ]
{ NOT DEFERRABLE | [ DEFEREABLE ] { IINITIALLY IMMEDIATE | INITIALLY DEFERED} }
FOR [ EACH ] { ROW | STATEMENT }
[ WHEN { condition }]
EXECUTE PROCEDURE function_name ( arguments )

• 创建触发器相当于将上述逻辑绑定到某表的某个操作上
• 本例中，对student表执行delete操作就会触发上述逻辑
• before是在delete执行前执行动作，after是在delete执行后执行逻辑，

CREATE TRIGGER delete_student_trigger
after delete on student
for each row execute procedure student_delete_trigger_fun();

查询触发器

查询触发器函数与触发器的关联关系

SELECT
  evtname AS 触发器名,
  evtevent AS 绑定事件,
  evtfoid::regproc AS 关联函数,
  evtenabled AS 启用状态
FROM pg_event_trigger;

查询触发器函数名

-- 仅查询DML触发器函数名
SELECT proname AS dml触发器函数名 FROM pg_proc WHERE prorettype = 'trigger'::regtype;

-- 仅查事件触发器函数名
SELECT proname AS 事件触发器函数名 FROM pg_proc WHERE prorettype = 'event_trigger'::regtype;

查询单个触发器函数的定义

-- 查询单个事件触发器函数（如abort_any_command）
SELECT pg_get_functiondef('abort_any_command') AS 函数完整定义;

-- 查询单个DML触发器函数（如之前的fn_trigger_data_log）
SELECT pg_get_functiondef('fn_trigger_data_log') AS 函数完整定义;

批量查询触发器函数的定义

-- 批量查询所有事件触发器函数定义
SELECT
  proname AS 函数名,
  pg_get_functiondef(oid) AS 完整创建语句
FROM pg_proc
WHERE prorettype = 'event_trigger'::regtype;


-- 批量查询所有DML触发器函数定义
SELECT
  proname AS 函数名,
  pg_get_functiondef(oid) AS 完整创建语句
FROM pg_proc
WHERE prorettype = 'trigger'::regtype;

案例二：行触发器

• 这个触发器的效果是，在emp表上的任何插入、更新、删除一行的动作都被记录（即审计）在emp_audit表中。表中会记录操作时间、操作用户、操作类型。

-- 创建员工表
CREATE TABLE emp1 (
    empname           text NOT NULL,
    salary            integer
);

-- 创建审计表
CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary            integer
);

-- 创建触发器函数
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        -- 如果对emp表中记录进行删除操作，会记录操作类型为D，操作时间、操作用户还有操作的数据内容
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit SELECT 'D', now(), current_user, OLD.*;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit SELECT 'U', now(), current_user, NEW.*;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit SELECT 'I', now(), current_user, NEW.*;
        END IF;
        RETURN NULL;
    END;
$emp_audit$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp1
    FOR EACH ROW EXECUTE FUNCTION process_emp_audit();

• 插入、删除数据，在审计表中会有记录
  

案例三：事件触发器

• 这个触发器会阻止所有ddl语言执行

-- 创建触发器函数
CREATE OR REPLACE FUNCTION abort_any_command()
returns event_trigger
language plpgsql
AS $$
BEGIN
    -- 发出异常，ddl命令不会执行。
    -- RAISE NOTICE则是发出消息，不会阻止命令执行
    RAISE EXCEPTION 'command % is disabled', tg_tag;
END;
$$;

-- 创建触发器
CREATE EVENT TRIGGER abort_DDL ON DDL_command_start
EXECUTE FUNCTION abort_any_command();

-- 禁止触发器
ALTER EVENT TRIGGER abort_DDL DISABLE;

• “CONTEXT: PL/pgSQL function abort_any_command() line 3 at RAISE”是PG的异常上下文。如果不想显示该内容，可以修改postgresql.conf中的client_min_messages = error。表示显示error级别以上的异常，不包括error级别