(openGauss每日一练第 18天)：openGauss触发器不同类型的创建及其他定义

1.创建源表及触发表

--1、使用gsql登录openGauss
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=#

--2、创建源表
omm=# CREATE TABLE test_trigger_src_tbl(id1 INT, id2 INT, id3 INT) ;
CREATE TABLE

--3、创建触发表
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触发器

--创建触发器函数
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 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;
UPDATE 1

--查看触发操作生效

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)

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;

CREATE FUNCTION
--创建delete触发器
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

--执行delete触发事件并检查触发结果
omm=# DELETE FROM test_trigger_src_tbl WHERE id1=100;
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 | tgconstrindid | tgconstraint | tgdeferrable | t
ginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner
---------+------------------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+--
--------------+---------+--------+--------+--------+---------
   16402 | insert_trigger         |  16408 |      7 | O         | f            |             0 |             0 |            0 | f            | f
              |       0 |        | \x     |        |      10
   16402 | update_trigger         |  16410 |     17 | O         | f            |             0 |             0 |            0 | f            | f
              |       0 |        | \x     |        |      10
   16402 | delete_trigger_renamed |  16412 |     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

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;
 id1 | id2 | id3
-----+-----+-----
 100 | 200 | 300
(1 row)

omm=# SELECT * FROM test_trigger_des_tbl;
 id1 | id2 | id3
-----+-----+-----
(0 rows)
--禁用当前表上所有触发器
omm=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER ALL;
ALTER TABLE

7.删除触发器

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=# DROP TRIGGER insert_trigger ON test_trigger_src_tbl;
DROP TRIGGER

--验证
omm=# select * from PG_TRIGGER;
 tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | 
tgnargs | tgattr | tgargs | tgqual | tgowner 
---------+--------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+-
--------+--------+--------+--------+---------
(0 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   |              | 
Has OIDs: no
Options: orientation=row, compression=no

练习：

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

--创建源表和出发表
omm=# CREATE TABLE tbs01_trigger(id1 INT, name2 varchar);
CREATE TABLE

omm=# CREATE TABLE tbs02_trigger(id1 INT, name2 varchar);
CREATE TABLE

--创建触发器函数
omm=# CREATE OR REPLACE FUNCTION tbs01_insert_func() RETURNS TRIGGER AS
omm-# $$
omm$# DECLARE
omm$# BEGIN
omm$# INSERT INTO tbs02_trigger VALUES(NEW.id1, NEW.name2);
omm$# RETURN NEW;
omm$# END
omm$# $$ LANGUAGE PLPGSQL;
CREATE FUNCTION

--创建INSERT触发器
omm=# CREATE TRIGGER insert_trigger
omm-# BEFORE INSERT ON tbs01_trigger
omm-# FOR EACH ROW
omm-# EXECUTE PROCEDURE tbs01_insert_func();
CREATE TRIGGER

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

--插入数据
omm=# INSERT INTO tbs01_trigger VALUES(222,'xiaozhu');
INSERT 0 1

--查询源表
omm=# SELECT * FROM tbs01_trigger;
 id1 |  name2  
-----+---------
 222 | xiaozhu
(1 row)

--查询出发表
omm=# SELECT * FROM tbs02_trigger;
 id1 |  name2  
-----+---------
 222 | xiaozhu
(1 row)

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

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

omm=# \dS+ tbs01_trigger
                          Table "public.tbs01_trigger"
 Column |       Type        | Modifiers | Storage  | Stats target | Description 
--------+-------------------+-----------+----------+--------------+-------------
 id1    | integer           |           | plain    |              | 
 name2  | character varying |           | extended |              | 
Triggers:
    insert_trigger BEFORE INSERT ON tbs01_trigger FOR EACH ROW EXECUTE PROCEDURE tbs01_insert_func()
Has OIDs: no
Options: orientation=row, compression=no

4.重命名触发器

omm=# ALTER TRIGGER insert_trigger ON tbs01_trigger RENAME TO insert_trigger_new;
ALTER TRIGGER

--验证触发器名称是否更改成功
omm=# select * from PG_TRIGGER;
 tgrelid |       tgname       | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tgini
tdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner 
---------+--------------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+------
----------+---------+--------+--------+--------+---------
   16414 | insert_trigger_new |  16426 |      7 | O         | f            |             0 |             0 |            0 | f            | f    
          |       0 |        | \x     |        |      10
(1 row)

omm=# \dS+ tbs01_trigger
                          Table "public.tbs01_trigger"
 Column |       Type        | Modifiers | Storage  | Stats target | Description 
--------+-------------------+-----------+----------+--------------+-------------
 id1    | integer           |           | plain    |              | 
 name2  | character varying |           | extended |              | 
Triggers:
    insert_trigger_new BEFORE INSERT ON tbs01_trigger FOR EACH ROW EXECUTE PROCEDURE tbs01_insert_func()
Has OIDs: no
Options: orientation=row, compression=no

5.删除触发器

omm=# DROP TRIGGER insert_trigger_new ON tbs01_trigger;
DROP TRIGGER

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

omm=# \dS+ tbs01_trigger
                          Table "public.tbs01_trigger"
 Column |       Type        | Modifiers | Storage  | Stats target | Description 
--------+-------------------+-----------+----------+--------------+-------------
 id1    | integer           |           | plain    |              | omm=# 
 name2  | character varying |           | extended |              | 
Has OIDs: no
Options: orientation=row, compression=no