openGauss每日一练第18天 | 学习心得体会

学习openGauss触发器

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

CREATE TABLE mytest_trigger_src_tbl(id1 INT, id2 INT, id3 INT) ;
CREATE TABLE mytest_trigger_des_tbl(id1 INT, id2 INT, id3 INT);
创建insert触发函数
CREATE OR REPLACE FUNCTION mytri_insert_func() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
INSERT INTO mytest_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);
RETURN NEW;
END
$$ LANGUAGE PLPGSQL;
创建insert触发器
CREATE TRIGGER insert_trigger
BEFORE INSERT ON mytest_trigger_src_tbl
FOR EACH ROW
EXECUTE PROCEDURE mytri_insert_func();

omm=# CREATE TABLE mytest_trigger_src_tbl(id1 INT, id2 INT, id3 INT) ;
CREATE TABLE
omm=# CREATE TABLE mytest_trigger_des_tbl(id1 INT, id2 INT, id3 INT);
CREATE TABLE
omm=# omm=# 
omm=# 
CREATE OR REPLACE FUNCTION mytri_insert_func() RETURNS TRIGGER AS
omm-# $$
omm$# omm$# DECLARE
omm$# BEGIN
INSERT INTO mytest_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);
omm$# RETURN NEW;
omm$# END
$$ LANGUAGE PLPGSQL;omm$# 
CREATE FUNCTION
omm=# 
omm=# CREATE TRIGGER insert_trigger
omm-# BEFORE INSERT ON mytest_trigger_src_tbl
omm-# FOR EACH ROW
omm-# EXECUTE PROCEDURE mytri_insert_func();
CREATE TRIGGER

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

INSERT INTO mytest_trigger_src_tbl VALUES(100,200,300);
SELECT * FROM mytest_trigger_src_tbl;
SELECT * FROM mytest_trigger_des_tbl;
ALTER TABLE mytest_trigger_src_tbl DISABLE TRIGGER insert_trigger;
INSERT INTO mytest_trigger_src_tbl VALUES(100,200,300);
SELECT * FROM mytest_trigger_src_tbl;
SELECT * FROM mytest_trigger_des_tbl;

omm=# INSERT INTO mytest_trigger_src_tbl VALUES(100,200,300);
INSERT 0 1
omm=# SELECT * FROM mytest_trigger_src_tbl;
 id1 | id2 | id3 
-----+-----+-----
 100 | 200 | 300
(1 row)

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

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

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

select * from PG_TRIGGER;
\dS+ mytest_trigger_src_tbl

omm=# select * from PG_TRIGGER;
 tgrelid |     tgname     | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdefe
rrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner 
---------+----------------+--------+--------+-----------+--------------+---------------+---------------+--------------+-------


-------+----------------+---------+--------+--------+--------+---------
   16405 | insert_trigger |  16411 |      7 | D         | f            |             0 |             0 |            0 | f     
       | f              |       0 |        | \x     |        |      10
   16417 | insert_trigger |  16423 |      7 | D         | f            |             0 |             0 |            0 | f     
       | f              |       0 |        | \x     |        |      10
(2 rows)omm=# \dS+ mytest_trigger_src_tbl
                Table "public.mytest_trigger_src_tbl"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id1    | integer |           | plain   |              | 
 id2    | integer |           | plain   |              | 
 id3    | integer |           | plain   |              | 
Disabled triggers:
    insert_trigger BEFORE INSERT ON mytest_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE mytri_insert_func()
Has OIDs: no
Options: orientation=row, compression=no

4.重命名触发器

ALTER TRIGGER delete_trigger ON mytest_trigger_src_tbl RENAME TO delete_trigger_renamed;

omm=# CREATE OR REPLACE FUNCTION TRI_DELETE_FUNC() RETURNS TRIGGER AS
omm-# $$
omm$# DECLARE
omm$# BEGIN
omm$# DELETE FROM mytest_trigger_des_tbl WHERE id1=OLD.id1;
omm$# RETURN OLD;
omm$# END
omm$# $$ LANGUAGE PLPGSQL;
CREATE FUNCTION
omm=# CREATE TRIGGER delete_trigger
omm-# BEFORE DELETE ON mytest_trigger_src_tbl
omm-# FOR EACH ROW
omm-# EXECUTE PROCEDURE tri_delete_func();
CREATE TRIGGER
omm=# DELETE FROM mytest_trigger_src_tbl WHERE id1=100;
DELETE 0
omm=# ALTER TRIGGER delete_trigger ON mytest_trigger_src_tbl RENAME TO delete_trigger_renamed;
ALTER TRIGGER

5.删除触发器

DROP TRIGGER insert_trigger ON mytest_trigger_src_tbl;
DROP TRIGGER delete_trigger_renamed ON mytest_trigger_src_tbl;