暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / openGauss每日一练第18天|触发器使用

openGauss每日一练第18天|触发器使用

原创 lqkitten 2021-12-18
1174

学习openGauss触发器

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

课程学习
连接数据库

#第一次进入等待15秒
#数据库启动中…

su - omm
gsql -r

1.创建源表及触发表

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

2.insert触发器

–创建触发器函数

omm=# 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 FUNCTION

–创建INSERT触发器

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

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

omm=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300);
INSERT 0 1

–查看触发操作生效

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)

3.update触发器

–创建触发器函数

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

–创建update触发器

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

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

omm=# UPDATE test_trigger_src_tbl SET id3=400 WHERE id1=100;
omm=# UPDATE 1

–查看触发操作生效

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

omm=# SELECT * FROM test_trigger_des_tbl;


omm=#  id1 | id2 | id3 
-----+-----+-----
 100 | 200 | 400

4.delete触发器

–创建触发器函数

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

–创建delete触发器

omm-# BEFORE DELETE ON test_trigger_src_tbl
omm-# FOR EACH ROW
omm-# EXECUTE PROCEDURE tri_delete_func();
CREATE TRIGGER

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

omm=# DELETE 1

–查看触发操作生效

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

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

5.修改触发器

–重命名

omm=# ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed;
ALTER TRIGGER

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

omm=# select * from PG_TRIGGER;
 tgrelid |         tgname         | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrin
did | tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner 
---------+------------------------+--------+--------+-----------+--------------+---------------+-----------
----+--------------+--------------+----------------+---------+--------+--------+--------+---------
   16389 | insert_trigger         |  16395 |      7 | O         | f            |             0 |           
  0 |            0 | f            | f              |       0 |        | \x     |        |      10
   16389 | update_trigger         |  16397 |     17 | O         | f            |             0 |           
  0 |            0 | f            | f              |       0 |        | \x     |        |      10
   16389 | delete_trigger_renamed |  16399 |     11 | O         | f            |             0 |           
  0 |            0 | f            | f              |       0 |        | \x     |        |      10
(3 rows)

–查看表上的所有触发器

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

                 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

6.禁用触发器
–禁用insert_trigger触发器

omm=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER insert_trigger;
ALTER TABLE
–执行INSERT

omm=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300);
INSERT 0 1

–查看触发操作没有生效

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

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

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

omm=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER ALL;
ALTER TABLE

7.删除触发器

DROP TRIGGER
omm=# DROP TRIGGER delete_trigger_renamed ON test_trigger_src_tbl;      
DROP TRIGGER

课程作业

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

omm=# create table src_tbl(c1 int,c2 int,c3 varchar(3));
CREATE TABLE
omm=# create table des_tbl(id1 int,idc varchar(3));
CREATE TABLE
omm=#  CREATE OR REPLACE FUNCTION  ins_func() RETURNS TRIGGER AS
omm-#   $$
omm$#   DECLARE
omm$#    BEGIN
omm$#   INSERT INTO des_tbl VALUES(NEW.c1, NEW.c3);
omm$#    END
omm$#   RETURN NEW;
omm$#   $$ LANGUAGE PLPGSQL;
CREATE FUNCTION
omm=# CREATE TRIGGER ins_trigger
omm-#   BEFORE INSERT ON src_tbl
omm-#   FOR EACH ROW
omm-#  EXECUTE PROCEDURE ins_func();
CREATE TRIGGER

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

omm=# insert into src_tbl values(1,2,'tim');
INSERT 0 1
omm=# select * from src_tbl;
omm=#  c1 | c2 | c3  
----+----+-----
  1 |  2 | tim
(1 row)
omm=# select * from des_tbl;
 id1 | idc 
-----+-----
   1 | tim
(1 row)
omm=# alter table src_tbl disable trigger ins_trigger;
ALTER TABLE
omm=# insert into src_tbl values(2,10,'joe');
INSERT 0 1
omm=# select * from src_tbl;


omm=#  c1 | c2 | c3  
----+----+-----
  1 |  2 | tim
  2 | 10 | joe
(2 rows)  
omm=# select * from des_tbl;
 id1 | idc 
-----+-----
   1 | tim
(1 row)

触发器禁用后，新插入数据没有写到目标表

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

omm=# select * from pg_trigger;
 tgrelid |     tgname     | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tg
constraint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner 
---------+----------------+--------+--------+-----------+--------------+---------------+---------------+---
-----------+--------------+----------------+---------+--------+--------+--------+---------
   16389 | insert_trigger |  16395 |      7 | D         | f            |             0 |             0 |   
         0 | f            | f              |       0 |        | \x     |        |      10
   16401 | ins_trigger    |  16407 |      7 | D         | f            |             0 |             0 |   
         0 | f            | f              |       0 |        | \x     |        |      10
(2 rows)
omm=# select * from pg_trigger;
 tgrelid |     tgname     | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tg
constraint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner 
---------+----------------+--------+--------+-----------+--------------+---------------+---------------+---
-----------+--------------+----------------+---------+--------+--------+--------+---------
   16389 | insert_trigger |  16395 |      7 | D         | f            |             0 |             0 |   
         0 | f            | f              |       0 |        | \x     |        |      10
   16401 | ins_trigger    |  16407 |      7 | D         | f            |             0 |             0 |   
         0 | f            | f              |       0 |        | \x     |        |      10
(2 rows)

omm=# \dS+ src_tbl
                              Table "public.src_tbl"
 Column |         Type         | Modifiers | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+--------------+-------------
 c1     | integer              |           | plain    |              | 
 c2     | integer              |           | plain    |              | 
 c3     | character varying(3) |           | extended |              | 
Disabled triggers:
    ins_trigger BEFORE INSERT ON src_tbl FOR EACH ROW EXECUTE PROCEDURE ins_func()
Has OIDs: no
Options: orientation=row, compression=no

4.重命名触发器

ALTER TRIGGER

5.删除触发器

omm=# drop trigger trig_ins on src_tbl;
DROP TRIGGER
opengauss
最后修改时间：2022-04-05 18:37:21
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论