openGauss每日一练第 18 天 |触发器及课后作业

openGauss 每日一练第 18 天学习打卡，学习 openGauss 触发器基本操作！

学习目标

学习 openGauss 定义游标

前面每日一练链接：

openGauss每日一练第 1 天 | 数据库和表的基本操作（一）
openGauss每日一练第 2 天 | 数据库和表的基本操作（二）
openGauss每日一练第 3 天 | 前三课作业实操练习
openGauss每日一练第 4 天 | 角色管理及课后作业
openGauss每日一练第 5 天 | 用户管理及课后作业
openGauss每日一练第 6 天 | 模式管理及课后作业
openGauss每日一练第 7 天 | 表空间管理及课后作业
openGauss每日一练第 8 天 | 分区表管理及课后作业
openGauss每日一练第 9 天 | 普通表索引管理及课后作业
openGauss每日一练第 10 天 | 分区表索引管理及课后作业
openGauss每日一练第 11 天 | 视图管理及课后作业
openGauss每日一练第 12 天 | 自定义数据类型管理及课后作业
openGauss每日一练第 13 天 | 数据导入操作及课后作业
openGauss每日一练第 14 天 | 数据导出操作及课后作业
openGauss每日一练第 15 天 | 定义存储过程和函数及课后作业
openGauss每日一练第 16 天 | 事务控制及课后作业
openGauss每日一练第 17 天 | 定义游标及课后作业

课程学习

学习 openGauss 定义游标

为了处理SQL语句，存储过程进程分配一段内存区域来保存上下文联系，游标是指向上下文区域的句柄或指针。借助游标，存储过程可以控制上下文区域的变化。

连接数据库

#第一次进入等待15秒
su - omm
gsql -r

1.创建源表及触发表

CREATE TABLE test_trigger_src_tbl(id1 INT, id2 INT, id3 INT) ;
CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);

2.insert触发器

–创建触发器函数

CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);
RETURN NEW;
END
$$ LANGUAGE PLPGSQL;

–创建INSERT触发器

CREATE TRIGGER insert_trigger
BEFORE INSERT ON test_trigger_src_tbl
FOR EACH ROW
EXECUTE PROCEDURE tri_insert_func();

–执行INSERT触发事件并检查触发结果

INSERT INTO test_trigger_src_tbl VALUES(100,200,300);

–查看触发操作生效

SELECT * FROM test_trigger_src_tbl;
SELECT * FROM test_trigger_des_tbl;

3.update触发器

–创建触发器函数

CREATE OR REPLACE FUNCTION tri_update_func() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
UPDATE test_trigger_des_tbl SET id3 = NEW.id3 WHERE id1=OLD.id1;
RETURN OLD;
END
$$ LANGUAGE PLPGSQL;

–创建update触发器

CREATE TRIGGER update_trigger
AFTER UPDATE ON test_trigger_src_tbl
FOR EACH ROW
EXECUTE PROCEDURE tri_update_func();

–执行update触发事件并检查触发结果

UPDATE test_trigger_src_tbl SET id3=400 WHERE id1=100;

–查看触发操作生效

SELECT * FROM test_trigger_src_tbl;
SELECT * FROM test_trigger_des_tbl;

4.delete触发器

–创建触发器函数

CREATE OR REPLACE FUNCTION TRI_DELETE_FUNC() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
DELETE FROM test_trigger_des_tbl WHERE id1=OLD.id1;
RETURN OLD;
END
$$ LANGUAGE PLPGSQL;

–创建delete触发器

CREATE TRIGGER delete_trigger
BEFORE DELETE ON test_trigger_src_tbl
FOR EACH ROW
EXECUTE PROCEDURE tri_delete_func();

–执行delete触发事件并检查触发结果

DELETE FROM test_trigger_src_tbl WHERE id1=100;

–查看触发操作生效

SELECT * FROM test_trigger_src_tbl;
SELECT * FROM test_trigger_des_tbl;  

5.修改触发器

–重命名

ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed;

–在系统表PG_TRIGGER中查看触发器

select * from PG_TRIGGER;

–查看表上的所有触发器

\dS+ test_trigger_src_tbl

6.禁用触发器

–禁用insert_trigger触发器

ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER insert_trigger;

–执行INSERT

INSERT INTO test_trigger_src_tbl VALUES(100,200,300);

–查看触发操作没有生效

SELECT * FROM test_trigger_src_tbl;
SELECT * FROM test_trigger_des_tbl;

–禁用当前表上所有触发器

ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER ALL;

7.删除触发器

DROP TRIGGER update_trigger ON test_trigger_src_tbl;
DROP TRIGGER delete_trigger_renamed ON test_trigger_src_tbl;      

课程作业

1.创建源表和触发表，在源表上创建insert触发器，创建操作触发表的触发器函数

CREATE TABLE jiekexu_trigger_tbl(id1 INT, id2 INT, id3 INT);
CREATE TABLE jiekexu_trigger_tb2(id1 INT, id2 INT, id3 INT);

--创建触发器函数
CREATE OR REPLACE FUNCTION trig_insert_func() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
INSERT INTO jiekexu_trigger_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);
RETURN NEW;
END
$$ LANGUAGE PLPGSQL;

--创建INSERT触发器
CREATE TRIGGER insert_trigger
BEFORE INSERT ON jiekexu_trigger_tbl
FOR EACH ROW
EXECUTE PROCEDURE trig_insert_func();

2.在源表上执行insert操作，查看触发操作是否生效；禁用触发器后，再次查看触发操作是否生效

–-执行INSERT触发事件并检查触发结果

INSERT INTO jiekexu_trigger_tbl VALUES(100,200,300);

–查看触发操作生效

SELECT * FROM jiekexu_trigger_tbl;
SELECT * FROM jiekexu_trigger_tb2;  

–禁用insert_trigger触发器

ALTER TABLE jiekexu_trigger_tbl DISABLE TRIGGER insert_trigger;

–执行INSERT

INSERT INTO jiekexu_trigger_tbl VALUES(100,200,300);

–查看触发操作没有生效

SELECT * FROM jiekexu_trigger_tbl;
SELECT * FROM jiekexu_trigger_tb2;

3.使用系统表PG_TRIGGER和\dS+查看触发器

select * from PG_TRIGGER;
\dS+ jiekexu_trigger_tbl;

jiekexu=# select * from PG_TRIGGER;
 tgrelid |     tgname     | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr
 | tgargs | tgqual | tgowner 
---------+----------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+---------+-------
-+--------+--------+---------
   16390 | insert_trigger |  16396 |      7 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |       
 | \x     |        |      10
(1 row)

jiekexu=# \dS+ jiekexu_trigger_tbl;
                 Table "public.jiekexu_trigger_tbl"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id1    | integer |           | plain   |              | 
 id2    | integer |           | plain   |              | 
 id3    | integer |           | plain   |              | 
Triggers:
    insert_trigger BEFORE INSERT ON jiekexu_trigger_tbl FOR EACH ROW EXECUTE PROCEDURE trig_insert_func()
Has OIDs: no
Options: orientation=row, compression=no

4.重命名触发器

ALTER TRIGGER insert_trigger ON jiekexu_trigger_tbl RENAME TO delete_trigger_renamed;

jiekexu=# select * from PG_TRIGGER;
 tgrelid |         tgname         | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnargs 
| tgattr | tgargs | tgqual | tgowner 
---------+------------------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+---------
+--------+--------+--------+---------
   16390 | delete_trigger_renamed |  16396 |      7 | O         | f            |             0 |             0 |            0 | f            | f              |       0 
|        | \x     |        |      10
(1 row)

5.删除触发器

DROP TRIGGER delete_trigger_renamed ON jiekexu_trigger_tbl;  

欧耶，第十八课触发器实操及课后作业练习题完成啦！第十九课见！！！