PostgreSQL教程：触发器

系统管理员 2023-10-16 05:23 9阅读 0赞

触发器Trigger，是由事件触发的一种存储过程

当对标进行`insert，update，delete，truncate`操作时，会触发表的Trigger（看触发器的创建时指定的事件）

构建两张表，学生信息表，学生分数表。

在删除学生信息的同时，自动删除学生的分数。

先构建表信息，填充数据

create table student(
        id int,
        name varchar(32)
    );
    create table score(
        id int,
        student_id int,
        math_score numeric,
        english_score numeric,
        chinese_score numeric
    );
    insert into student (id,name) values (1,'张三');
    insert into student (id,name) values (2,'李四');
    insert into score (id,student_id,math_score,english_score,chinese_score) values(1,1,66,66,66);
    
    insert into score (id,student_id,math_score,english_score,chinese_score)
    values (2,2,55,55,55);
    
    select * from student;
    select * from score;

为了完成级联删除的操作，需要编写pl/sql。

先查看一下PGSQL支持的plsql，查看一下PGSQL的`plsql`语法

[ <<label>> ]
    [ DECLARE
        declarations ]
    BEGIN
        statements
    END [ label ];

构建一个存储函数，测试一下plsql

-- 优先玩一下plsql
    -- $$可以理解为是一种特殊的单引号，避免你在declare，begin，end中使用单引号时，出现问题，
    -- 需要在编写后，在$$之后添加上当前内容的语言。
    create function test() returns int as $$
    declare
        money int := 10;
    begin
        return money;
    end;
    $$ language plpgsql;
    
    select test();

在简单了解了一下plpgsql的语法后，编写一个触发器函数。

触发器函数允许使用一些特殊变量

NEW
    数据类型是RECORD；该变量为行级触发器中的INSERT/UPDATE操作保持新数据行。在语句级别的触发器以及DELETE操作，这个变量是null。
    
    OLD
    数据类型是RECORD；该变量为行级触发器中的UPDATE/DELETE操作保持新数据行。在语句级别的触发器以及INSERT操作，这个变量是null。

构建一个删除学生分数的触发器函数。

-- 构建一个删除学生分数的触发器函数。
    create function trigger_function_delete_student_score() returns trigger as $$
    begin
        delete from score where student_id = old.id;
        return old;
    end;
    $$ language plpgsql;

开始构建触发器，在学生信息表删除时，执行前面声明的触发器函数

![image.png][]

CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
        ON table_name
        [ FROM referenced_table_name ]
        [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
        [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
        [ FOR [ EACH ] { ROW | STATEMENT } ]
        [ WHEN ( condition ) ]
        EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )
    
    where event can be one of:
    
        INSERT
        UPDATE [ OF column_name [, ... ] ]
        DELETE
        TRUNCATE

> 当 `CONSTRAINT`选项被指定，这个命令会创建一个 *约束触发器* 。这和一个常规触发器相同，不过触发该触发器的时机可以使用[SET CONSTRAINTS][]调整。约束触发器必须是表上的 `AFTER ROW`触发器。它们可以在导致触发器事件的语句末尾被引发或者在包含该语句的事务末尾被引发。在后一种情况中，它们被称作是被 *延迟* 。一个待处理的延迟触发器的引发也可以使用 `SET CONSTRAINTS`立即强制发生。当约束触发器实现的约束被违背时，约束触发器应该抛出一个异常。

描绘一波~~

-- 编写触发器，指定在删除某一行学生信息时，触发当前触发器，执行触发器函数
    create trigger trigger_student 
    after 
    delete 
    on student 
    for each row 
    execute function trigger_function_delete_student_score();
    -- 测试效果
    select * from student;
    select * from score;
    delete from student where id = 1;

[image.png]: https://img-blog.csdnimg.cn/img_convert/743d96aabcb621c85f158647c9049136.png
[SET CONSTRAINTS]: http://www.postgres.cn/docs/12/sql-set-constraints.html