在 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
:触发器的名称。BEFORE
或AFTER
:指定触发器在操作之前或之后执行。INSERT
、UPDATE
或DELETE
:指定触发器与哪种操作相关联。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
语句。BEGIN
和END
:定义函数的代码块。RETURN NEW;
:在BEFORE INSERT
或BEFORE UPDATE
触发器中,返回NEW
表示修改后的行数据。RETURN OLD;
:在AFTER UPDATE
或AFTER DELETEE
触发器中,返回OLD
表示修改前的行数据。RETURN NULL;
:在AFTER INSERT
或AFTER 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
表地址address
为Beijing
的学生,写入到一张新的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之间可以写什么样的语句
需要注意的是,在触发器中执行的语句应该符合 PostgreSQL 的语法规范,并且要注意触发器的执行时机和触发条件,以避免出现不必要的问题。