openGauss每日一练第18天 | 学习笔记

原创手机用户2634 2021-12-30

205

第十八课 openGauss触发器

学习目标

学习openGauss触发器

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

课程学习

连接数据库

#第一次进入等待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触发器，创建操作触发表的触发器函数

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

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

4.重命名触发器

5.删除触发器



CREATE TABLE tt1(id1 INT, id2 INT, id3 INT) ;
CREATE TABLE tt2(id1 INT, id2 INT, id3 INT);

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


CREATE TRIGGER insert_trigger1
BEFORE INSERT ON tt1
FOR EACH ROW
EXECUTE PROCEDURE tri_insert_func1();


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

SELECT * FROM tt1;
SELECT * FROM tt2;


select * from PG_TRIGGER;
\dS+ tt1

ALTER TABLE tt1 DISABLE TRIGGER insert_trigger1;

INSERT INTO tt1 VALUES(200,300,400);
SELECT * FROM tt1;
SELECT * FROM tt2;



ALTER TRIGGER insert_trigger1 ON tt1 RENAME TO insert_trigger2;

DROP TRIGGER insert_trigger2 ON tt1;


\df
drop function tri_insert_func1;
drop function tri_insert_func;
drop function tri_update_func1;
drop function tri_delete_func;

复制

课后作业记录


omm=# 
omm=# CREATE TABLE test_trigger_src_tbl(id1 INT, id2 INT, id3 INT) ;
CREATE TABLE
omm=# CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);
CREATE TABLE
omm=# 
omm=# \d
                                List of relations
 Schema |         Name         | Type  | Owner |             Storage              
--------+----------------------+-------+-------+----------------------------------
 public | test_trigger_des_tbl | table | omm   | {orientation=row,compression=no}
 public | test_trigger_src_tbl | table | omm   | {orientation=row,compression=no}
(2 rows)

omm=# 
omm=# CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
omm-# $$
omm$# DECLARE
omm$# BEGIN
omm$# INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);
omm$# END
omm$# RETURN NEW;
omm$# $$ LANGUAGE PLPGSQL;
CREATE FUNCTION
omm=# 
omm=# 
omm=# CREATE TRIGGER insert_trigger
omm-# BEFORE INSERT ON test_trigger_src_tbl
omm-# FOR EACH ROW
omm-# EXECUTE PROCEDURE tri_insert_func();
CREATE TRIGGER
omm=# 
omm=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300);
INSERT 0 1
omm=# 
omm=# SELECT * FROM test_trigger_src_tbl;
 id1 | id2 | id3 
-----+-----+-----
 100 | 200 | 300
(1 row)

omm=# SELECT * FROM test_trigger_des_tbl;
 id1 | id2 | id3 
-----+-----+-----
 100 | 200 | 300
(1 row)

omm=# 
omm=# 
omm=# CREATE OR REPLACE FUNCTION tri_update_func() RETURNS TRIGGER AS
omm-# $$
omm$# DECLARE
omm$# BEGIN
omm$# UPDATE test_trigger_des_tbl SET id3 = NEW.id3 WHERE id1=OLD.id1;
omm$# RETURN OLD;
omm$# END
omm$# $$ LANGUAGE PLPGSQL;
CREATE FUNCTION
omm=# 
omm=# 
omm=# CREATE TRIGGER update_trigger
omm-# AFTER UPDATE ON test_trigger_src_tbl
omm-# FOR EACH ROW
omm-# EXECUTE PROCEDURE tri_update_func();
CREATE TRIGGER
omm=# 
omm=# UPDATE test_trigger_src_tbl SET id3=400 WHERE id1=100;
UPDATE 1
omm=# 
omm=# SELECT * FROM test_trigger_src_tbl;
 id1 | id2 | id3 
-----+-----+-----
 100 | 200 | 400
(1 row)

omm=# SELECT * FROM test_trigger_des_tbl;
 id1 | id2 | id3 
-----+-----+-----
 100 | 200 | 400
(1 row)

omm=# 
omm=# CREATE OR REPLACE FUNCTION TRI_DELETE_FUNC() RETURNS TRIGGER AS
omm-# $$
omm$# DECLARE
omm$# BEGIN
omm$# DELETE FROM test_trigger_des_tbl WHERE id1=OLD.id1;
omm$# RETURN OLD;
omm$# END
omm$# $$ LANGUAGE PLPGSQL;
CREATE FUNCTION
omm=# 
omm=# 
omm=# CREATE TRIGGER delete_trigger
omm-# BEFORE DELETE ON test_trigger_src_tbl
omm-# FOR EACH ROW
omm-# EXECUTE PROCEDURE tri_delete_func();
CREATE TRIGGER
omm=# 
omm=# 
omm=# DELETE FROM test_trigger_src_tbl WHERE id1=100;
DELETE 1
omm=# 
omm=# SELECT * FROM test_trigger_src_tbl;
 id1 | id2 | id3 
-----+-----+-----
(0 rows)

omm=# SELECT * FROM test_trigger_des_tbl;  


omm=#  id1 | id2 | id3 
-----+-----+-----
(0 rows)
omm=# ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed;
ALTER TRIGGER
omm=# omm=# 
select * from PG_TRIGGER;
 tgrelid |         tgname         | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgn
args | tgattr | tgargs | tgqual | tgowner 
---------+------------------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+----
-----+--------+--------+--------+---------
   16397 | insert_trigger         |  16403 |      7 | O         | f            |             0 |             0 |            0 | f            | f              |    
   0 |        | \x     |        |      10
   16397 | update_trigger         |  16405 |     17 | O         | f            |             0 |             0 |            0 | f            | f              |    
   0 |        | \x     |        |      10
   16397 | delete_trigger_renamed |  16407 |     11 | O         | f            |             0 |             0 |            0 | f            | f              |    
   0 |        | \x     |        |      10
(3 rows)

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

omm=# 
omm=# 
omm=# 
omm=# 
omm=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER insert_trigger;
ALTER TABLE
omm=# 
omm=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300);
INSERT 0 1
omm=# 
omm=# SELECT * FROM test_trigger_src_tbl;
 id1 | id2 | id3 
-----+-----+-----
 100 | 200 | 300
(1 row)

omm=# SELECT * FROM test_trigger_des_tbl;
 id1 | id2 | id3 
-----+-----+-----
(0 rows)

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


omm=# 
omm=# 
omm=# drop trigger insert_trigger on test_trigger_src_tbl;
DROP TRIGGER
omm=# 



omm=# 
omm=# 
omm=# CREATE TABLE tt1(id1 INT, id2 INT, id3 INT) ;
CREATE TABLE
omm=# CREATE TABLE tt2(id1 INT, id2 INT, id3 INT);
CREATE TABLE
omm=# 
omm=# CREATE OR REPLACE FUNCTION tri_insert_func1() RETURNS TRIGGER AS $$
omm$# DECLARE
omm$# BEGIN
omm$# INSERT INTO tt2 VALUES(NEW.id1, NEW.id2, NEW.id3);
omm$# RETURN NEW;
omm$# omm$# END
$$ LANGUAGE PLPGSQL;
CREATE FUNCTION
omm=# 
omm=# 
omm=# 
omm=# CREATE TRIGGER insert_trigger1
omm-# BEFORE INSERT ON tt1
omm-# FOR EACH ROW
omm-# EXECUTE PROCEDURE tri_insert_func1();
CREATE TRIGGER
omm=# 
omm=# 
omm=# INSERT INTO tt1 VALUES(100,200,300);
INSERT 0 1
omm=# 
omm=# SELECT * FROM tt1;
 id1 | id2 | id3 
-----+-----+-----
 100 | 200 | 300
(1 row)

omm=# SELECT * FROM tt2;
omm=#  id1 | id2 | id3 
-----+-----+-----
 100 | 200 | 300
(1 row)


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

omm=# \dS+ test_trigger_src_tbl
                 Table "public.test_trigger_src_tbl"
 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=# 
omm=# 
omm=# \dS+ tt1
 id1    | integer |           | plain   |              | 
 id2    | integer |           | plain   |              | 
 id3    | integer |           | plain   |              | 
Triggers:
    insert_trigger1 BEFORE INSERT ON tt1 FOR EACH ROW EXECUTE PROCEDURE tri_insert_func1()
Has OIDs: no
Options: orientation=row, compression=no

omm=#                          Table "public.tt1"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------

omm=# 
omm=# 
omm=# ALTER TABLE tt1 DISABLE TRIGGER insert_trigger1;
ALTER TABLE
omm=# 
omm=# INSERT INTO tt1 VALUES(200,300,400);
INSERT 0 1
omm=# 
omm=# SELECT * FROM tt1;
 id1 | id2 | id3 
-----+-----+-----
 100 | 200 | 300
 200 | 300 | 400
(2 rows)

omm=# SELECT * FROM tt2;
omm=#  id1 | id2 | id3 
-----+-----+-----
 100 | 200 | 300
(1 row)


omm=# 
omm=# 
omm=# ALTER TRIGGER insert_trigger1 ON tt1 RENAME TO insert_trigger2;
ALTER TRIGGER
omm=# 
omm=# DROP TRIGGER insert_trigger2 ON tt1;
DROP TRIGGER
omm=# 
omm=# \dS+ tt1
 id2    | integer |           | plain   |              | 
 id3    | integer |           | plain   |              | 
Has OIDs: no
Options: orientation=row, compression=no

omm=#                          Table "public.tt1"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id1    | integer |           | plain   |              | 

omm=# 
omm=# 
omm=# 
omm=# \df
                                                List of functions
 Schema |       Name       | Result data type | Argument data types |  Type   | fencedmode | propackage | prokind 
--------+------------------+------------------+---------------------+---------+------------+------------+---------
 public | tri_delete_func  | trigger          |                     | trigger | f          | f          | f
 public | tri_insert_func  | trigger          |                     | trigger | f          | f          | f
 public | tri_insert_func1 | trigger          |                     | trigger | f          | f          | f
 public | tri_update_func  | trigger          |                     | trigger | f          | f          | f
(4 rows)

omm=# 
omm=# 
omm=# 
omm=# drop function tri_delete_func;
omm=# DROP FUNCTION

omm=# drop function tri_insert_func;
DROP FUNCTION
omm=# 
omm=# drop function tri_insert_func1;
DROP FUNCTION
omm=# 
omm=# drop function tri_update_func;
DROP FUNCTION
omm=# 


复制

opengauss

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者