任务十六 openGauss逻辑结构:触发器管理

任务目标

通过本任务,初步掌握各种类型触发器的管理和应用。

实施步骤

触发器是一个特殊的存储过程,与某个特定的表相关联,在对该表执行INSERT、DELETE或UPDATE语句之前或者之后,自动激活执行。
使用触发器具有以下几个好处:
1)触发器可用来实现数据完整性约束(检查列值是否在某个范围内),也可以用来实现表间的数据完整性约束(补偿不保持依赖的表分解)。
2)触发器可以基于某个表达式为列提供默认值,甚至可以为那些只能使用常量默认值进行定义的列类型提供值。
3)触发器可以在行删除或更新之前先检查行的当前内容。这种能力能完成许多任务,如记录已有行的更改情况。
要创建触发器,可以使用CREATE TRIGGER语句,其语法格式如下:

CREATE [CONSTRAINT] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} {event [OR…]}ON table_name[FROM referenced_table_name]{NOT DEFERRABLE | [DEFERRABLE] {INITIALLY IMMEDIATE | INITIALLY DEFERRED}}[FOR [EACH] {ROW | STATEMENT}][WHEN(condition)]EXECUTE PROCEDURE function_name(arguments);

其中event包含以下几种:

INSERT
UPDATE [OF column_name[, …]]
DELETE
RUNCATE

在创建触发器的定义里,需要指明触发它的语句类型(INSERT、UPDATE或DELETE),以及是在行被修改之前触发,还是在之后触发。

一、测试openGauss的触发器

本实验测试openGauss的触发器功能:每当往测试表test1中插入一条记录时,会在测试表test2中插入一条日志记录。

1.创建测试表

执行下面的命令和SQL语句,创建测试表test1和test2:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r
--  第一步，创建测试表，用于测试触发器
DROP TABLE IF EXISTS test1;
CREATE TABLE test1( col11  int  PRIMARY KEY,col12  char(10));
DROP TABLE IF EXISTS test2;
CREATE TABLE test2( col21  timestamp,col22  varchar(200));

2.创建触发器对应的函数

执行下面的SQL语句,创建触发器对应的函数:

--  第二步，创建触发器对应的存储函数
CREATE OR REPLACE FUNCTION testtrigger()
RETURNS TRIGGER
AS $$
BEGININSERT INTO test2 values(NOW(),'record inserted!') ;RETURN OLD;
END;
$$
LANGUAGE plpgsql;

3.创建触发器

执行下面的命令,创建触发器:

--  第三步，创建触发器
--  该触发器在往表test1每插入1条新数据时，会往表test2插入1条日志记录
CREATE TRIGGER mytrigger AFTER INSERT ON test1 
FOR EACH ROW EXECUTE PROCEDURE testtrigger();

4.测试触发器

执行下面的命令,测试刚刚创建的触发器:

--  第四步，测试触发器
select * from test1;
select * from test2;
INSERT INTO test1 values(1,'test');
select * from test1;
select * from test2;

二、触发器的类型

1.行级触发器

前面刚刚创建的触发器就是一个行级触发器。执行下面的语句,再次测试行级触发器:

--  再次执行下面的语句，一次插入2条记录到测试表
select * from test1;
select * from test2;
INSERT INTO test1 values(2,'test'),(3,'test');
select * from test1;
select * from test2;

从输出可以看出,在表test1中每插入一行,就会在表test2中增加一条记录。这就是行级触发器。
如果插入语句执行失败,行级触发器会怎么样呢?

--  继续执行下面的语句，该语句会违法主键约束因此会失败！
select * from test1;
select * from test2;
INSERT INTO test1 values(1,'test');
select * from test1;
select * from test2;

由于该触发器是AFTER触发器(往表成功插人行后才会执行该触发器对应的函数),因此INSERT语句执行失败(因为违反了主键约束),触发器不会被执行。
执行下面的命令,清除刚才创建的行级触发器的相关对象:

drop trigger mytrigger on test1;
drop function testtrigger;
drop table test1;
drop table test2;
\q

2.语句级触发器

执行下面的命令系列,创建一个语句级触发器:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r
--  第一步，创建测试表，用于测试触发器
DROP TABLE IF EXISTS test1;
CREATE TABLE test1( col11  int  PRIMARY KEY,col12  char(10));
DROP TABLE IF EXISTS test2;
CREATE TABLE test2( col21  timestamp,col22  varchar(200));

接下来,执行下面的命令,创建触发器对应的函数:

--  第二步，创建触发器对应的存储函数
CREATE OR REPLACE FUNCTION testtrigger()
RETURNS TRIGGER
AS $$
BEGININSERT INTO test2 values(NOW(),'record inserted!') ;RETURN OLD;
END;
$$
LANGUAGE plpgsql;

再接下来,执行下面的命令,创建语句级触发器:

--  第三步，创建语句级触发器
--  该触发器在往表test1插入新数据时，会记录日志到表test2中
CREATE TRIGGER mytrigger AFTER INSERT ON test1 
FOR EACH STATEMENT EXECUTE PROCEDURE testtrigger();

最后,执行下面的命令,测试语句级触发器:

--  第四步，测试语句级触发器
select * from test1;
select * from test2;
INSERT INTO test1 values(2,'test'),(3,'test');
select * from test1;
select * from test2;

从输出可以看到,尽管在一条INSERT语句插入了两行数据,但是语句级触发器只执行一次。
执行下面的命令,清除刚才创建的语句级AFTER触发器的相关对象:

drop trigger mytrigger on test1;
drop function testtrigger;
drop table test1;
drop table test2;
\q

3.AFTER触发器和 BEFORE触发器

前面语句测试了AFTER触发器:AFTER触发器在完成插入操作后再执行触发器动作。当语句执行失败时,AFTER触发器不会被执行。
下面是测试BEFORE触发器的步骤。
1)首先执行下面的命令创建一个BEFORE触发器:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r
--  第一步，创建测试表，用于测试触发器
DROP TABLE IF EXISTS test;
CREATE TABLE test AS
SELECT * FROM INSTRUCTOR;

2)然后执行下面的命令创建触发器对应的函数:

--  第二步，创建触发器对应的存储函数
CREATE OR REPLACE FUNCTION testtrigger()
RETURNS TRIGGER
AS $$
BEGINNEW.NAME=NEW.NAME||NEW.ID;RETURN NEW;
END;
$$
LANGUAGE plpgsql;

3)接下来执行下面的命令创建行级BEFORE触发器:

--  第三步，创建行级BEFORE触发器
--  该触发器在往表test插入新数据时，会记录日志到表test2中
CREATE TRIGGER mytrigger BEFORE INSERT ON test 
FOR EACH ROW EXECUTE PROCEDURE testtrigger();

4)最后执行下面的命令,测试建行级BEFORE触发器:

--  第四步，测试语句级BEFORE触发器
INSERT INTO test(ID,NAME,DEPT_NAME,SALARY)VALUES('88888','test','Comp. Sci.',85000);
select * from test;

我们发现,INSERT语句中要插人的值是“88888,test,Comp.Sci.,85000”,但实际插入到表中的值经过BEFORE触发器提前被更改为namelid。在将行插入表test之前执行触发器的动作,修改要插入的值,然后再将修改后的值插入到表test中:这只能通过 BEFORE触发器来完成。

执行下面的命令,清理数据:

delete from test where id='88888';
\q