openGauss每日一练第 18 天 | 学习openGauss触发器

👉openGauss SQL学习参考

学习目标

学习openGauss触发器

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

课程学习

连接openGauss

root@modb:~# su - omm
omm@modb:~$ gsql -r   

1.创建源表及触发表

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

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触发事件并检查触发结果
mydb=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300);
INSERT 0 1
mydb=#

--查看触发操作生效
mydb=# SELECT * FROM test_trigger_src_tbl;
 id1 | id2 | id3 
-----+-----+-----
 100 | 200 | 300
(1 row)

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

mydb=# 

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触发事件并检查触发结果
mydb=# UPDATE test_trigger_src_tbl SET id3=400 WHERE id1=100;
UPDATE 1
mydb=# 

--查看触发操作生效
mydb=# SELECT * FROM test_trigger_src_tbl;
 id1 | id2 | id3 
-----+-----+-----
 100 | 200 | 400
(1 row)

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

mydb=# 

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触发事件并检查触发结果
mydb=# DELETE FROM test_trigger_src_tbl WHERE id1=100;
DELETE 1
mydb=#

--–查看触发操作生效
mydb=# SELECT * FROM test_trigger_src_tbl;
 id1 | id2 | id3 
-----+-----+-----
(0 rows)

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

mydb=# 

5.修改触发器

--重命名
mydb=# ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed;
ALTER TRIGGER
mydb=#

--在系统表PG_TRIGGER中查看触发器
mydb=# select * from PG_TRIGGER;
 tgrelid |         tgname         | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner 
---------+------------------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+---------+--------+--------+--------+---------
   16585 | insert_trigger         |  16591 |      7 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |      10
   16585 | update_trigger         |  16593 |     17 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |      10
   16585 | delete_trigger_renamed |  16595 |     11 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |      10
(3 rows)

mydb=# 

--查看表上的所有触发器
mydb=# \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

mydb=# 

6.禁用触发器

--禁用 insert_trigger 触发器
mydb=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER insert_trigger;
ALTER TABLE
mydb=# \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()
    update_trigger AFTER UPDATE ON test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE tri_update_func()
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

mydb=# --Disabled triggers

--执行INSERT
mydb=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300);
INSERT 0 1
mydb=# 

--查看触发操作没有生效
mydb=# SELECT * FROM test_trigger_src_tbl;
 id1 | id2 | id3 
-----+-----+-----
 100 | 200 | 300
(1 row)

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

mydb=# 

--禁用当前表上所有触发器
mydb=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER ALL;
ALTER TABLE
mydb=# \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   |              | 
Disabled 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

mydb=# 

7.删除触发器

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

mydb=# drop table test_trigger_des_tbl ;
DROP TABLE
mydb=# drop table test_trigger_src_tbl ;
DROP TABLE
mydb=# 

课后作业

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

mydb=# CREATE TABLE t1_src(id INT, name varchar(10));
CREATE TABLE
mydb=# CREATE TABLE t1_des(id INT, name varchar(10));
CREATE TABLE
mydb=#
 
CREATE OR REPLACE FUNCTION trg_t1_src() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
  INSERT INTO t1_des VALUES(NEW.id, NEW.name );
  RETURN NEW;
END
$$ LANGUAGE PLPGSQL;

mydb=# CREATE TRIGGER t1_src_ins_trg BEFORE INSERT ON t1_src FOR EACH ROW EXECUTE PROCEDURE trg_t1_src();
CREATE TRIGGER
mydb=# 

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

mydb=# INSERT INTO t1_src VALUES(1,'aa');
INSERT 0 1
mydb=# select * from t1_src ;
 id | name 
----+------
  1 | aa
(1 row)

mydb=# select * from t1_des ;
 id | name 
----+------
  1 | aa
(1 row)

--禁用触发器
mydb=# ALTER TABLE t1_src DISABLE TRIGGER t1_src_ins_trg;
ALTER TABLE
mydb=# INSERT INTO t1_src VALUES(2,'b');
INSERT 0 1
mydb=# select * from t1_src ;
 id | name 
----+------
  1 | aa
  2 | b
(2 rows)

mydb=# select * from t1_des ;
 id | name 
----+------
  1 | aa
(1 row)

mydb=# 

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

mydb=# select * from PG_TRIGGER;
 tgrelid |     tgname     | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tg
nargs | tgattr | tgargs | tgqual | tgowner 
---------+----------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+---
------+--------+--------+--------+---------
   16597 | t1_src_ins_trg |  16603 |      7 | D         | f            |             0 |             0 |            0 | f            | f              |   
    0 |        | \x     |        |      10
(1 row)

mydb=# \dS+ t1_src 
                               Table "public.t1_src"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 id     | integer               |           | plain    |              | 
 name   | character varying(10) |           | extended |              | 
Disabled triggers:
    t1_src_ins_trg BEFORE INSERT ON t1_src FOR EACH ROW EXECUTE PROCEDURE trg_t1_src()
Has OIDs: no
Options: orientation=row, compression=no

mydb=# 

4.重命名触发器

mydb=# ALTER TRIGGER t1_src_ins_trg ON t1_src RENAME TO t1_src_ins_trg_bak;
ALTER TRIGGER
mydb=# 
mydb=# \dS+ t1_src 
                               Table "public.t1_src"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 id     | integer               |           | plain    |              | 
 name   | character varying(10) |           | extended |              | 
Disabled triggers:
    t1_src_ins_trg_bak BEFORE INSERT ON t1_src FOR EACH ROW EXECUTE PROCEDURE trg_t1_src()
Has OIDs: no
Options: orientation=row, compression=no

mydb=# 

5.删除触发器

mydb=# DROP TRIGGER t1_src_ins_trg_bak ON t1_src;
DROP TRIGGER
mydb=# DROP TABLE t1_des;
DROP TABLE
mydb=# DROP TABLE t1_src;
DROP TABLE
mydb=#