openGauss每日一练第18天 课程笔记和作业

数据库环境

openGauss:2.0.0 - 数据库实训平台

学习目标

触发器是对应用动作的响应机制，当应用对一个对象发起DML操作时，就会产生一个触发事件（Event），如果该对象上拥有该事件对应的触发器，那么就会检查触发器的触发条件（Condition）是否满足，如果满足触发条件，那么就会执行触发动作（Action）

学习笔记

• insert/update/delete触发函数

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;

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;

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;

• insert/update/delete触发器

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

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

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

课后作业

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

omm=# create table src_tb1(id1 int,id2 int,id3 int);
CREATE TABLE
omm=# create table des_tb1(id1 int,id2 int,id3 int);
CREATE TABLE

omm=# create or replace function tri_insert_func() returns trigger as
omm-# $$
omm$# declare
omm$# begin
omm$# insert into des_tb1 values(NEW.id1, NEW.id2, NEW.id3);
omm$# return NEW;
omm$# end
omm$# $$ language plpgsql;
CREATE FUNCTION

omm=# create trigger insert_trigger
omm-# before insert on src_tb1
omm-# for each row
omm-# execute procedure tri_insert_func();
CREATE TRIGGER

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

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

omm=# insert into src_tb1 values(100,101,102),(200,201,202);
INSERT 0 2
omm=# select * from src_tb1;
 id1 | id2 | id3 
-----+-----+-----
 100 | 101 | 102
 200 | 201 | 202
(2 rows)

omm=# select * from des_tb1;
 id1 | id2 | id3 
-----+-----+-----
 100 | 101 | 102
 200 | 201 | 202
(2 rows)


omm=# alter table src_tb1 disable trigger insert_trigger;
ALTER TABLE

omm=# insert into src_tb1 values(300,301,302);
INSERT 0 1

omm=# select * from src_tb1;
 id1 | id2 | id3 
-----+-----+-----
 100 | 101 | 102
 200 | 201 | 202
 300 | 301 | 302
(3 rows)

omm=# select * from des_tb1;
 id1 | id2 | id3 
-----+-----+-----
 100 | 101 | 102
 200 | 201 | 202
(2 rows)

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

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

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

4.重命名触发器

omm=# alter trigger insert_trigger on src_tb1 rename to insert_trigger_new;
ALTER TRIGGER

omm=# \dS+ src_tb1;                       Table "public.src_tb1"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id1    | integer |           | plain   |              | 
 id2    | integer |           | plain   |              | 
 id3    | integer |           | plain   |              | 
Disabled triggers:
    insert_trigger_new BEFORE INSERT ON src_tb1 FOR EACH ROW EXECUTE PROCEDURE tri_insert_func()
Has OIDs: no
Options: orientation=row, compression=no

5.删除触发器

omm=# drop trigger insert_trigger_new on src_tb1;
DROP TRIGGER

omm=# \dS+ src_tb1;
                       Table "public.src_tb1"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id1    | integer |           | plain   |              | 
 id2    | integer |           | plain   |              | 
 id3    | integer |           | plain   |              | 
Has OIDs: no
Options: orientation=row, compression=no

omm=# select * from PG_TRIGGER;
 tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner 
---------+--------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+---------+--------+--------+--------+---------
(0 rows)

学习资源

• openGauss SQL学习参考资料
• 每日一练：openGauss数据库在线实训课程
• openGauss每日一练 | 21期养成好习惯，提升技术能力！
• 墨天轮Markdown编辑器使用介绍
• 墨天轮数据库在线实训平台V1.0操作手册
• 墨天轮数据社区

欢迎各位同学一起来交流学习心得！