OpenGauss上的触发器创建和数据库系统概论有较大的区别——需要先创建一个函数(返回的是触发器,然后在函数里面写条件),然后再创建一个触发器(触发器调用的是触发函数)。
触发函数和触发器的功能不同,触发器是调用触发函数实现功能,因此触发函数的内容要写入触发的条件。
一开始可能接触这种写法会感觉不适应,但其实这种写法减少了触发器内部的耦合,相当于将条件定义单独分离成一个函数,如果后期要修改条件,只需要重新编写触发函数,而不用更改整个触发器,更加便于后期的更改和维护,因此同学们要积极适应。
在文章的末尾,我详细介绍了触发器及触发器的功能和作用。
(4)
先创建一个函数,返回的是触发器:函数的逻辑用于确保教师的工资不低于 4000 元。如果工资低于 4000 元,则函数会自动将工资设置为 4000 元。
CREATE OR REPLACE FUNCTION check_teacher_salary()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.SAL < 4000 THEN
NEW.SAL := 4000;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
经试验检测:一定要进入postgres$#才能成功:
REATE OR REPLACE FUNCTION:创建一个新的函数,如果同名函数已经存在,则用新的函数替换旧的函数。
AS $:指定函数体的开始位置,并且使用$符号作为标识符的分隔符。
BEGIN:函数体的开始位置。
$ LANGUAGE plpgsql:指定使用plpgsql语言编写函数体,并且使用$符号作为标识符的分隔符。
然后再创建触发器:
CREATE TRIGGER insert_or_update_sal
BEFORE INSERT OR UPDATE ON Teacher
FOR EACH ROW
EXECUTE PROCEDURE check_teacher_salary();
EXECUTE PROCEDURE check_teacher_salary();指定在触发器触发时执行的函数(PROCEDURE
)。
效果如下图:
(5)
(6)
查看代码如下图:
SELECT t.tgname AS trigger_name,p.proname AS function_name,p.prosrc AS function_code
FROM pg_trigger t
JOIN pg_proc p ON t.tgfoid = p.oid
WHERE t.tgrelid = 'Teacher'::regclass;
pg_trigger表包含了触发器的相关信息;
pg_proc表包含了存储过程(也包括触发器函数)的相关信息;
t.tgfoid是触发器关联的函数的 OID(对象标识符);
p.oid是存储过程的 OID;
t.tgrelid是触发器关联的表的 OID;
'Teacher'::regclass是名为Teacher的表的 OID
效果如下图:
(7)
以下代码是创建函数:
CREATE OR REPLACE FUNCTION delete_dept_cascade()
RETURNS TRIGGER AS $$
BEGIN
DELETE FROM teacher
WHERE deptno = OLD.deptno;
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
OLD是一个特殊的关键字,用于在触发器中表示被修改或删除的行的旧值。在触发器中,OLD关键字只能用于BEFORE和AFTER触发器,并且只能用于DELETE和UPDATE操作。
以下代码是创建触发器:
CREATE TRIGGER trg_delete_dept_cascade
AFTER DELETE
ON dept
FOR EACH ROW
XECUTE PROCEDURE delete_dept_cascade();
(8)
首先要创建Course表:
CREATE TABLE Course(Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40) NOT NULL,
Cpno CHAR(4),Ccredit SMALLINT,
FOREIGN KEY(Cpno) REFERENCES Course(Cno));
插入Course表内容:
INSERT INTO Course VALUES('1','数据库','5',4),('2','数学','',2),('3',' 信息系统','1',4),
('4','操作系统','6',3),('5','数据结构','7',4),('6','数据处理','',2),('7','PASCAL语言','6',4);
然后创建Student表:
CREATE TABLE Student(Sno CHAR(9) PRIMARY KEY,Sname CHAR(20) NOT NULL,
Ssex CHAR(5),Sage SMALLINT,Sdept CHAR(20));
插入Student表的内容:
INSERT INTO Student VALUES('201215121', '李勇', '男', 20, '计算机科学'),
('201215122', '刘晨', '女', 19, '计算机科学'),('201215123', '王敏', '女', 18, '数学'),
('201215125', '张立', '男', 19, '信息科学');
创建SC表:
CREATE TABLE SC(Sno CHAR(9),Cno CHAR(4),Grade SMALLINT CHECK(Grade>=0 AND Grade<=100),
PRIMARY KEY(Sno,Cno),FOREIGN KEY(Sno) REFERENCES Student(Sno),
FOREIGN KEY(Cno) REFERENCES Course(Cno));
插入SC表的内容:
INSERT INTO SC VALUES('201215121', '1', 92),('201215121', '2', 85),
('201215121', '3', 88),('201215122', '2', 90),('201215122', '3', 80);
创建SC_U表:
CREATE TABLE SC_U(Sno CHAR(9),Cno CHAR(4),Oldgrade
NUMBER(3),Newgrade NUMBER(3),FOREIGN KEY(Sno) REFERENCES
Student(Sno),FOREIGN KEY(Cno) REFERENCES Course(Cno));
创建触发函数:
CREATE OR REPLACE FUNCTION update_sc_audit()
RETURNS TRIGGER AS $$
BEGIN
IF(NEW.Grade >= OLD.Grade*1.1) THEN
INSERT INTO SC_U(Sno,Cno,Oldgrade,Newgrade)
VALUES(OLD.Sno,OLD.cno,OLD.Grade,NEW.Grade);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
创建触发器:
CREATE TRIGGER tri_update_sc
AFTER UPDATE OF Grade ON SC
FOR EACH ROW
EXECUTE PROCEDURE update_sc_audit();
更新效果如下图,可见正常工作:
(9)
下面是代码:
ALTER TRIGGER tri_update_sc ON SC RENAME TO update_sc_tri;
下图是更名效果:
(10)
删除触发函数和触发器:
删除SC表重新创建:
删除SC_U表重新创建:
创建空操作触发函数:
重建触发器:
再插入数据,自然没有作用了:
(11)
删除触发器如下:
触发器的本质是一个函数,是一种能在特定事件发生时自动执行的PL/pgSQL函数,它可以捕获特定的INSERT、UPDATE或DELETE操作,并在操作之前或之后执行某些逻辑,可以实现对数据库的自动化控制。
触发器的作用:
1、数据完整性控制。2、数据审计和追踪。3、数据缓存。4、数据分发和同步。
触发器使用场景:1、完整性控制,如对数据的插入、更新和删除操作进行限制或校验。2、数据审计追踪:记录数据的修改历史和操作记录,对敏感数据进行审计。3、数据缓存:对需要频繁查询的数据进行缓存,提高查找效率。4、数据分发和同步:需要数据自动同步或分发到其它数据库或表中。