PostgreSql创建触发器并增加IF判断条件

2023年 9月 17日 55.3k 0

b686da793b0444699ab724e3cee54936_2.png

在 PostgreSQL 中,可以使用触发器(Trigger)来在表上定义自定义的插入(INSERT)、更新(UPDATE)和删除(DELETE)操作的行为。触发器是与表相关联的特殊函数,它们在指定的操作发生时自动执行。

PostgreSQL创建触发器的语法

创建触发器

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
[FOR EACH ROW]
[WHEN (condition)]
EXECUTE FUNCTION function_name();
  • trigger_name:触发器的名称。
  • BEFOREAFTER:指定触发器在操作之前或之后执行。
  • INSERTUPDATEDELETE:指定触发器与哪种操作相关联。
  • table_name:触发器所属的表名。
  • FOR EACH ROW:指定触发器为每一行执行。
  • WHEN (condition):可选项,指定触发器执行的条件。
  • EXECUTE FUNCTION function_name():指定触发器执行的函数。

创建触发器函数

CREATE FUNCTION function_name()
RETURNS TRIGGER AS $$
BEGIN
    -- 触发器的逻辑代码
    RETURN NEW; -- RETURN OLD; 或 RETURN NULL;
END;
$$ LANGUAGE plpgsql;
  • function_name:触发器函数的名称。
  • RETURNS TRIGGER:指定函数返回一个触发器对象。
  • $$ 是一种用于定义多行字符串的语法,用于将函数体中的代码块括起来,以便将多行代码作为一个字符串传递给 CREATE FUNCTION 语句。
  • BEGINEND:定义函数的代码块。
  • RETURN NEW;:在 BEFORE INSERTBEFORE UPDATE 触发器中,返回 NEW 表示修改后的行数据。
  • RETURN OLD;:在 AFTER UPDATEAFTER DELETEE 触发器中,返回 OLD 表示修改前的行数据。
  • RETURN NULL;:在 AFTER INSERTAFTER DELETE 触发器中,返回 NULL

删除触发器

DROP TRIGGER trigger_name ON table_name;
  • trigger_name:要删除的触发器的名称。
  • table_name:触发器所属的表名。

PostgreSQL创建触发器的案例

创建一张学生表

CREATE TABLE "public"."student" (
  "student_id" int4 NOT NULL DEFAULT nextval('student_student_id_seq'::regclass),
  "name" varchar(50) COLLATE "pg_catalog"."default" NOT NULL,
  "age" int4 NOT NULL,
  "gender" varchar(10) COLLATE "pg_catalog"."default" NOT NULL,
  "address" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
  "phone" varchar(20) COLLATE "pg_catalog"."default" NOT NULL,
  "grade" varchar(20) COLLATE "pg_catalog"."default" NOT NULL,
  CONSTRAINT "student_pkey" PRIMARY KEY ("student_id")
);
​
ALTER TABLE "public"."student" 
  OWNER TO "postgres";
COMMENT ON COLUMN "public"."student"."student_id" IS '学生id';
COMMENT ON COLUMN "public"."student"."name" IS '姓名';
COMMENT ON COLUMN "public"."student"."age" IS '年龄';
COMMENT ON COLUMN "public"."student"."gender" IS '性别';
COMMENT ON COLUMN "public"."student"."address" IS '地址';
COMMENT ON COLUMN "public"."student"."phone" IS '电话';
COMMENT ON COLUMN "public"."student"."grade" IS '年级';

创建触发器要求

student表地址addressBeijing的学生,写入到一张新的student_beijing表中,同步新增修改和删除。

CREATE TABLE "public"."student_beijing" (
  "student_id" int4 NOT NULL DEFAULT nextval('student_student_id_seq'::regclass),
  "name" varchar(50) COLLATE "pg_catalog"."default" NOT NULL,
  "age" int4 NOT NULL,
  "gender" varchar(10) COLLATE "pg_catalog"."default" NOT NULL,
  "address" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
  "phone" varchar(20) COLLATE "pg_catalog"."default" NOT NULL,
  "grade" varchar(20) COLLATE "pg_catalog"."default" NOT NULL,
  CONSTRAINT "student_beijing_pkey" PRIMARY KEY ("student_id")
);
​
ALTER TABLE "public"."student_beijing" 
  OWNER TO "postgres";
COMMENT ON COLUMN "public"."student_beijing"."student_id" IS '学生id';
COMMENT ON COLUMN "public"."student_beijing"."name" IS '姓名';
COMMENT ON COLUMN "public"."student_beijing"."age" IS '年龄';
COMMENT ON COLUMN "public"."student_beijing"."gender" IS '性别';
COMMENT ON COLUMN "public"."student_beijing"."address" IS '地址';
COMMENT ON COLUMN "public"."student_beijing"."phone" IS '电话';
COMMENT ON COLUMN "public"."student_beijing"."grade" IS '年级';

创建新增触发器

# 创建触发器函数
CREATE OR REPLACE FUNCTION student_insert_function() RETURNS TRIGGER AS $student_table$  
    BEGIN 
​
        IF 'Beijing' = new.address THEN
            INSERT INTO student_beijing (student_id,name,age,gender,address,phone,grade) VALUES (new.student_id,new.name,new.age,new.gender,new.address,new.phone,new.grade);
        END IF;
​
    RETURN NEW;   
    END;  
$student_table$ LANGUAGE plpgsql;
​
# 创建新增触发器
CREATE TRIGGER student_insert_trigger AFTER INSERT ON student  
FOR EACH ROW EXECUTE PROCEDURE student_insert_function();
​
# 删除触发器
DROP TRIGGER IF EXISTS student_insert_trigger ON student;
# 删除函数
DROP FUNCTION student_insert_function();

创建修改触发器

# 创建触发器函数
CREATE OR REPLACE FUNCTION student_update_function() RETURNS TRIGGER AS $student_table$  
    BEGIN 
​
        IF 'Beijing' = new.address THEN
            UPDATE student_beijing SET name = new.name,age = new.age,gender = new.gender,address = new.address,phone = new.phone,grade = new.grade WHERE student_id = old.student_id;
        END IF;
​
    RETURN NEW;   
    END;  
$student_table$ LANGUAGE plpgsql;
​
# 创建修改触发器
CREATE TRIGGER student_update_trigger AFTER UPDATE ON student  
FOR EACH ROW EXECUTE PROCEDURE student_update_function();
​
# 删除触发器
DROP TRIGGER IF EXISTS student_update_trigger ON student;
# 删除函数
DROP FUNCTION student_update_function();

创建删除触发器

# 创建触发器函数
CREATE OR REPLACE FUNCTION student_delete_function() RETURNS TRIGGER AS $student_table$  
    BEGIN 
​
        IF 'Beijing' = old.address THEN
            DELETE FROM student_beijing WHERE student_id = old.student_id;
        END IF;
​
    RETURN OLD;   
    END;  
$student_table$ LANGUAGE plpgsql;
​
​
# 创建删除触发器
CREATE TRIGGER student_delete_trigger AFTER DELETE ON student  
FOR EACH ROW EXECUTE PROCEDURE student_delete_function();
​
# 删除触发器
DROP TRIGGER IF EXISTS student_delete_trigger ON student;
# 删除函数
DROP FUNCTION student_delete_function();

测试触发器

student表新增数据

INSERT INTO student ("student_id", "name", "age", "gender", "address", "phone", "grade") VALUES (4, 'John Doe', 18, 'Male', 'Beijing', '1234567890', 'Grade 10');

student表修改数据

UPDATE student SET "name" = 'John Doe', "age" = 18, "gender" = 'Male', "address" = 'Beijing', "phone" = '1234567890', "grade" = 'Grade 11' WHERE "student_id" = 4;

student表删除数据

DELETE FROM student WHERE student_id = 4;

PostgreSQL使用触发器的优缺点

触发器的优点

  • 数据完整性:触发器可以用于实施数据完整性约束,例如在插入或更新数据之前进行验证。
  • 自动化操作:触发器可以自动执行一系列操作,无需手动干预。这可以提高开发效率并减少人为错误。
  • 数据一致性:通过触发器,可以确保数据库中的数据保持一致性,例如在更新操作时自动更新相关字段。
  • 日志记录:触发器可以用于记录数据库中的操作,例如在插入、更新或删除数据时记录相关信息。
  • 触发器的缺点

  • 复杂性:触发器的创建和管理可能会增加数据库的复杂性。触发器的逻辑必须正确且易于理解,以避免潜在的错误和混乱。
  • 性能影响:触发器的执行会增加数据库的负载,特别是在处理大量数据时。过多或复杂的触发器可能会导致性能下降。
  • 隐式操作:触发器的存在可能会导致一些隐式的操作,这可能会使代码更难以理解和维护。开发人员需要注意触发器的存在,并确保其行为符合预期。
  • 在使用触发器时,需要权衡其优点和缺点,并根据具体情况进行决策。触发器应该被谨慎使用,仅在确实需要自动化操作或确保数据完整性时使用。

    PostgreSQL创建触发器BEGIN和END之间可以写什么样的语句

  • SQL 查询语句:可以在触发器中执行 SELECT 语句来获取数据或进行计算。
  • 数据操作语句:可以在触发器中执行 INSERT、UPDATE 或 DELETE 语句来修改数据。
  • 变量声明和赋值语句:可以在触发器中声明变量,并使用赋值语句给变量赋值。
  • 控制流语句:可以在触发器中使用 IF、CASE、LOOP 等控制流语句来实现条件逻辑。
  • 函数调用语句:可以在触发器中调用自定义函数或内置函数来实现特定的逻辑。
  • 异常处理语句:可以在触发器中使用异常处理语句来处理可能发生的异常情况。
  • 其他 SQL 语句:可以在触发器中使用其他合法的 SQL 语句来满足特定需求。
  • 需要注意的是,在触发器中执行的语句应该符合 PostgreSQL 的语法规范,并且要注意触发器的执行时机和触发条件,以避免出现不必要的问题。

    相关文章

    JavaScript2024新功能:Object.groupBy、正则表达式v标志
    PHP trim 函数对多字节字符的使用和限制
    新函数 json_validate() 、randomizer 类扩展…20 个PHP 8.3 新特性全面解析
    使用HTMX为WordPress增效:如何在不使用复杂框架的情况下增强平台功能
    为React 19做准备:WordPress 6.6用户指南
    如何删除WordPress中的所有评论

    发布评论