#opengauss学习的第18天
1.创建源表和触发表，在源表上创建insert触发器，创建操作触发表的触发器函数
omm=# CREATE TABLE test_trigger_src_t1(id1 INT, id3 INT,name char(40)) ;
CREATE TABLE
omm=# CREATE TABLE test_trigger_des_t2(id1 INT, id3 INT,name char(40));
CREATE TABLE
omm=#
omm=# CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
omm-# $$
omm# DECLARE
omm# omm# BEGIN
INSERT INTO test_trigger_des_t2 VALUES(NEW.id1, NEW.id3,NEW.name);
RETURN NEW;
omm# END
omm# omm# $$ LANGUAGE PLPGSQL;
CREATE FUNCTION
omm=#
omm=# CREATE TRIGGER insert_tri
omm-# BEFORE INSERT ON test_trigger_src_t1
omm-# FOR EACH ROW
omm-# EXECUTE PROCEDURE tri_insert_func();
CREATE TRIGGER
omm=# INSERT INTO test_trigger_src_t1 VALUES(VALUES(100,300,‘lisi’);
INSERT 0 1
2.在源表上执行insert操作，查看触发操作是否生效；禁用触发器后，再次查看触发操作是否生效
omm=# SELECT * FROM test_trigger_src_t1;
omm=# id1 | id3 | name
-----±----±-----------------------------------------
100 | 300 | lisi
(1 row)
SELECT * FROM test_trigger_des_t2;
id1 | id3 | name
-----±----±-----------------------------------------
100 | 300 | lisi
omm=# ALTER TABLE test_trigger_src_t1 DISABLE TRIGGER insert_tri;
ALTER TABLE
omm=#
omm=# INSERT INTO test_trigger_src_t1 VALUES(1000,200,‘xiaopiaoliang’);
INSERT 0 1
omm=# SELECT * FROM test_trigger_src_t1;
id1 | id3 | name
------±----±-----------------------------------------
100 | 300 | lisi
200 | 400 | zhangsan
200 | 400 | zhangsan
1000 | 200 | xiaopiaoliang
(4 rows)
omm=# SELECT * FROM test_trigger_des_t2;
id1 | id3 | name
-----±----±-----------------------------------------
100 | 300 | lisi
200 | 400 | zhangsan
200 | 400 | zhangsan
3.使用系统表PG_TRIGGER和dS+查看触发器
omm=# \dS+ test_trigger_src_t1
Table “public.test_trigger_src_t1”
Column | Type | Modifiers | Storage | Stats target | Description
--------±--------------±----------±---------±-------------±------------
id1 | integer | | plain | |
id3 | integer | | plain | |
name | character(40) | | extended | |
Disabled triggers:
insert_tri BEFORE INSERT ON test_trigger_src_t1 FOR EACH ROW EXECUTE PROCEDURE tri_insert_func()
Has OIDs: no
Options: orientation=row, compression=no
omm=# select * from pg_trigger limit 1;
tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint |
tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner
---------±-----------±-------±-------±----------±-------------±--------------±--------------±-------------+
--------------±---------------±--------±-------±-------±-------±--------
16411 | insert_tri | 16395 | 7 | D | f | 0 | 0 | 0 |
f | f | 0 | | \x | | 10
4.重命名触发器
omm=# \dS+ test_trigger_src_t1
Table “public.test_trigger_src_t1”
Column | Type | Modifiers | Storage | Stats target | Description
--------±--------------±----------±---------±-------------±------------
id1 | integer | | plain | |
id3 | integer | | plain | |
name | character(40) | | extended | |
Disabled triggers:
insert_tri BEFORE INSERT ON test_trigger_src_t1 FOR EACH ROW EXECUTE PROCEDURE tri_insert_func()
Has OIDs: no
Options: orientation=row, compression=no
omm=# alter trigger insert_tri on test_trigger_src_t1 rename to insert_tri_rename;
ALTER TRIGGER
omm=# \dS+ test_trigger_src_t1
Table “public.test_trigger_src_t1”
Column | Type | Modifiers | Storage | Stats target | Description
--------±--------------±----------±---------±-------------±------------
id1 | integer | | plain | |
id3 | integer | | plain | |
name | character(40) | | extended | |
Disabled triggers:
omm=# insert_tri_rename BEFORE INSERT ON test_trigger_src_t1 FOR EACH ROW EXECUTE PROCEDURE tri_insert_func()
Has OIDs: no
Options: orientation=row, compression=no
5.删除触发器
omm=# drop trigger insert_tri_rename on test_trigger_src_t1 ;
DROP TRIGGER
omm=#
omm=# \dS+ test_trigger_src_t1
Table “public.test_trigger_src_t1”
Column | Type | Modifiers | Storage | Stats target | Description
--------±--------------±----------±---------±-------------±------------
id1 | integer | | plain | |
id3 | integer | | plain | |
name | character(40) | | extended | |
Has OIDs: no
Options: orientation=row, compression=no
